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