Subversion Repositories DevTools

--  UsedBySDKDetail.sql
--  Locate package versions that use the package specified by :PV_ID
--  Within an SDK specified by :SDK_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_state, 
        st.SDK_ID, 
        st.SDKTAG_NAME, 
        st.SDKTAG_ID, 
        pkg.pkg_name, 
        pv.PKG_VERSION, 
        pv.pv_id,
        pv.MODIFIER_ID, 
        pv.MODIFIED_STAMP,
        usr.FULL_NAME, 
        usr.USER_EMAIL,
        CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
FROM SDK_CONTENT sc,
  SDK_TAGS st,
  SDK_NAMES sn,
  PACKAGE_VERSIONS pv,
  PACKAGES pkg,
  USERS usr,
  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
  AND st.SDK_ID = :SDK_ID
  AND pv.pkg_id = pkg.pkg_id
  AND pv.MODIFIER_ID = usr.USER_ID(+)
ORDER BY UPPER(SDK_NAME), st.SDKTAG_ID