Subversion Repositories DevTools

-- UsedByPackageDetail.sql
-- Determine package versions that depend on a specific package name
-- Provide the package version id and version text
--  :PKG_ID   - Need packages that match this package ID
--  :PV_ID    - Base package. Provides v_ext and possibly exact match 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 pkg.pkg_name, 
       pkg.pkg_id , 
       ppv.pv_id, 
       ppv.pkg_version,
       pv.pkg_version as used_version,
       pv.pv_id as used_pv_id,
       ppv.DLOCKED, 
       ppv.MODIFIER_ID, 
       ppv.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 PACKAGE_DEPENDENCIES dep,
  package_versions pv,
  package_versions ppv,
  packages pkg,
  USERS usr,
   aa, bb
  Where dep.DPV_ID = pv.pv_id
  AND dep.PV_ID = ppv.pv_id
  AND ppv.PKG_ID = :PKG_ID
  AND pkg.pkg_id = ppv.pkg_id
  AND pv.MODIFIER_ID = usr.USER_ID(+)
  AND (pv.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)
  ORDER BY UPPER(pkg.pkg_name), UPPER(ppv.pkg_version) desc