Subversion Repositories DevTools

/* Compare BOM With Latest From Release Manager */
SELECT dmbom.pkg_id,
           dmbom.PROD_ID,
           dmbom.os_id,
           pkg.pkg_name AS PROD_NAME,
           nt.NODE_ICON,
           nn.NODE_NAME ||' / '|| os.OS_NAME ||' / ' AS PROD_LOCATION,
           dmbom.pkg_version AS PROD_VERSION,
           DECODE( dmbom.pkg_version,
                           rml.pkg_version, NULL,
                           CASE WHEN rml.pv_id < dmbom.prod_id THEN
                                        'Older ('|| rml.pkg_version ||')'
                                        ELSE
                                        rml.pkg_version
                                        END
                         ) AS RM_LATEST_VERSION,
           rml.pv_id AS LATEST_PROD_ID,
           ( CASE WHEN rml.pv_id < dmbom.prod_id THEN
                        NULL
                 ELSE
                        'checked'
                 END ) AS CHECKED
  FROM PACKAGES pkg,
           OPERATING_SYSTEMS os,
           NETWORK_NODES nn,
           NODE_TYPES nt,
           (
                /* Latest packages in Release Manager release */
                SELECT pv.pv_id, pv.pkg_id, pv.v_ext,
                           pv.pkg_version
                  FROM RELEASE_CONTENT rc,
                           PACKAGE_VERSIONS pv
                 WHERE rc.PV_ID = pv.PV_ID
                   AND rc.RTAG_ID = :RTAG_ID
           ) rml,
           (
                /* BOM Products */
                SELECT osc.PROD_ID, pv.pkg_id, pv.V_EXT, osc.OS_ID,
                           pv.pkg_version
                  FROM OS_CONTENTS osc,
                           OPERATING_SYSTEMS os,
                           BOM_CONTENTS bc,
                           PACKAGE_VERSIONS pv
                 WHERE osc.OS_ID = os.OS_ID
                   AND bc.NODE_ID = os.NODE_ID
                   AND osc.PROD_ID = pv.PV_ID
                   AND bc.BOM_ID = :BOM_ID
           AND pv.IS_PATCH IS NULL      
           ) dmbom,
           HIDE_PRODUCTS hp
 WHERE dmbom.pkg_id = pkg.pkg_id
   AND dmbom.OS_ID = os.OS_ID
   AND os.NODE_ID = nn.NODE_ID
   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
   AND rml.pkg_version IS NOT NULL
   AND rml.pv_id != dmbom.prod_id
   AND rml.pkg_id (+) = dmbom.pkg_id
   AND dmbom.PROD_ID = hp.PROD_ID
   AND dmbom.OS_ID = hp.OS_ID
   /* AND NVL( rml.v_ext (+) , 'LINK_NULL_VALUE' ) = NVL( dmbom.v_ext, 'LINK_NULL_VALUE' )   */
ORDER BY UPPER(pkg.pkg_name), UPPER(PROD_LOCATION)