Subversion Repositories DevTools

Rev

Rev 5947 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
5939 dpurdie 1 
-- UsedByProjects.sql
5946 dpurdie 2 
-- Generate summary data for projects that use this package version
3 
--  Match control via :MATCH
4 
--    :MATCH 0  - Match any  package name
5 
--    :MATCH 1  - Match Package name and Project Suffix
6 
--    :MATCH 2  - Match exactly the package. Name, Version and Suffix
5947 dpurdie 7 
 with
8 
  aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID),
9 
  bb AS (select pkg_id from package_versions where pv_id = :PV_ID)
119 ghuddy 10 
SELECT proj.PROJ_ID,
5939 dpurdie 11 
       proj.PROJ_NAME,
12 
       COUNT(ev.RTAG_ID) AS USED_COUNT
119 ghuddy 13 
  FROM ENVIRONMENT_VIEW ev,
5939 dpurdie 14 
       PACKAGE_VERSIONS pv,
15 
       RELEASE_TAGS rt,
5947 dpurdie 16 
       PROJECTS proj,
17 
       aa, bb
119 ghuddy 18 
 WHERE ev.PV_ID = pv.PV_ID
19 
   AND ev.RTAG_ID = rt.RTAG_ID
20 
   AND rt.PROJ_ID = proj.PROJ_ID
5946 dpurdie 21 
   AND (ev.PV_ID  = :PV_ID OR :MATCH != 2)
5947 dpurdie 22 
   AND (NVL(pv.v_ext,'LINK') in (  aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
23 
   AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
7570 dpurdie 24 
   AND rt.official != 'S'
25 
GROUP BY proj.PROJ_NAME, proj.PROJ_ID
26 
ORDER BY UPPER(proj.PROJ_NAME) ASC
5946 dpurdie 27