Subversion Repositories DevTools

/* rep_obsolete_packages.sql */
SELECT frc.pv_id, pkg.pkg_name, frc.pkg_version, frc.view_name, frc.view_id,
       frc.modifier, frc.modifier_email, frc.MODIFIED_STAMP,
       frc.insertor, frc.insertor_email, frc.INSERT_STAMP
  FROM  
        (  
        /* All released (locked) packages from a release MINUS 
            Products and Auto_Products  Bit-0 of SMODE
            3RDPARTY_PRODUCTS           Bit-1 of SMODE
            Those marked as deployable  Bit-2 of SMODE
            Those imported via an SDK
        */  
        SELECT pv.pkg_id, pv.v_ext
          FROM release_content rc,
               package_versions pv
         WHERE rc.rtag_id = :RTAG_ID  
           AND ( NOT rc.base_view_id = 5    OR ( BITAND (:SMODE, 1) = 0) )
           AND ( NOT rc.base_view_id = 2602 OR ( BITAND (:SMODE, 1) = 0) )
           AND ( NOT rc.base_view_id = 2381 OR ( BITAND (:SMODE, 2) = 0) )
           AND ( pv.is_deployable is NULL   OR ( BITAND (:SMODE, 4) = 0) )
           AND pv.dlocked = 'Y'
           AND rc.pv_id = pv.pv_id
         AND rc.SDKTAG_ID IS NULL
        MINUS
        /* All used by from a release */
        SELECT dpv.pkg_id, dpv.v_ext
          FROM release_content rc,
               package_dependencies dep,
            package_versions dpv
         WHERE rc.rtag_id = :RTAG_ID
           AND rc.pv_id = dep.pv_id  
           AND dep.dpv_id = dpv.pv_id
        ) nou,
        (
        /* Full release content */
        SELECT pv.pv_id, pv.pkg_id, pv.v_ext, pv.pkg_version, vi.view_name, vi.view_id,
               lmusr.full_name AS modifier, lmusr.user_email AS modifier_email, pv.MODIFIED_STAMP,
               arusr.full_name AS insertor, arusr.user_email AS insertor_email, rc.INSERT_STAMP
          FROM release_content rc,
               package_versions pv,
               views vi,
               users lmusr,
               users arusr
         WHERE rc.rtag_id = :RTAG_ID  
           AND rc.pv_id = pv.pv_id
           AND rc.base_view_id = vi.view_id
           AND pv.MODIFIER_ID = lmusr.user_id
           AND rc.INSERTOR_ID = arusr.user_id
        ) frc,
        packages pkg
WHERE nou.pkg_id = frc.pkg_id
  AND NVL(nou.v_ext, 'LINK_A_NULL') = NVL(frc.v_ext, 'LINK_A_NULL')
  AND frc.pkg_id = pkg.pkg_id
ORDER BY UPPER(frc.view_name), UPPER(pkg.pkg_name)