Subversion Repositories DevTools

-- UsedByProjects.sql
-- Generate summary data for projects that use a package.suffix
-- It can generate two types of data
-- PV_ID == -1 -- Any package.suffix
-- PV_ID != -1 -- Only package that match this specific package-version
SELECT proj.PROJ_ID,
       proj.PROJ_NAME,
       COUNT(ev.RTAG_ID) AS USED_COUNT  
  FROM ENVIRONMENT_VIEW ev,
       PACKAGE_VERSIONS pv,
       RELEASE_TAGS rt,
       PROJECTS proj
 WHERE ev.PV_ID = pv.PV_ID
   AND pv.PKG_ID = :PKG_ID
   AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( :V_EXT, '|LINK_A_NULL|')
   AND ev.RTAG_ID = rt.RTAG_ID
   AND rt.PROJ_ID = proj.PROJ_ID
   AND (ev.pv_id = :PV_ID OR :PV_ID = -1)
 GROUP BY proj.PROJ_ID, proj.PROJ_NAME  
 ORDER BY UPPER(proj.PROJ_NAME)