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   0 == Don't match exact 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
  Where dep.DPV_ID = pv.pv_id
  AND dep.PV_ID = ppv.pv_id
  AND ppv.PKG_ID = :PKG_ID
  AND pv.PKG_ID in ( select pkg_id from package_versions where pv_id = :PV_ID )
  AND NVL(pv.v_ext,'LINK') in ( select NVL(v_ext,'LINK') from package_versions where pv_id = :PV_ID )
  AND pkg.pkg_id = ppv.pkg_id
  AND (pv.pv_id = :PV_ID OR :MATCH = 0)
  AND pv.MODIFIER_ID = usr.USER_ID(+)
  ORDER BY UPPER(pkg.pkg_name), UPPER(ppv.pkg_version) desc