Subversion Repositories DevTools

--  UsedBySDKSummary.sql
--  Locate package versions that use the package specified by :PV_ID
--  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 DISTINCT SDK_NAME, st.SDK_ID, COUNT(st.SDK_ID) as COUNT
FROM SDK_CONTENT sc,
  SDK_TAGS st,
  SDK_NAMES sn,
  PACKAGE_VERSIONS pv,
  aa,bb
WHERE sc.SDKTAG_ID = st.SDKTAG_ID 
  AND (sc.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 st.SDK_ID = sn.SDK_ID
  AND sc.PV_ID = pv.PV_ID
GROUP BY SDK_NAME  , st.SDK_ID
ORDER BY UPPER(SDK_NAME)