Subversion Repositories DevTools

-- UsedByThisSnapshot.sql
-- Generate summary data for projects that use this package version
--  Match control via :MATCH
--    :MATCH 0  - Match any  package name
--    :MATCH 1  - Match Package name and Project Suffix
--    :MATCH 2  - Match exactly the package. Name, Version and Suffix
 with 
  aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID),
  bb AS (select pkg_id from package_versions where pv_id = :PV_ID)
SELECT rtp.rtag_name,
       rtp.RTAG_ID,
       COUNT(rtp.RTAG_ID) AS USED_COUNT  
  FROM ENVIRONMENT_VIEW ev,
       PACKAGE_VERSIONS pv,
       RELEASE_TAGS rt,
       RELEASE_TAGS rtp,
       PROJECTS proj,
       aa, bb
 WHERE ev.PV_ID = pv.PV_ID
   AND ev.RTAG_ID = rt.RTAG_ID
   AND rtp.PROJ_ID = proj.PROJ_ID
   AND rtp.proj_id = :PROJ_ID
   AND rtp.rtag_id = rt.parent_rtag_id
   AND (ev.PV_ID  = :PV_ID OR :MATCH != 2)
   AND (NVL(pv.v_ext,'LINK') in (  aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
   AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
   AND rt.official = 'S'
GROUP BY rtp.rtag_name, rtp.RTAG_ID 
ORDER BY UPPER(rtp.rtag_name) ASC