Subversion Repositories DevTools

Rev

Rev 5925 | Rev 5946 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5925 Rev 5939
Line 1... Line 1...
1 
-- UsedByPackageDetail.sql
 1 
-- UsedByPackageDetail.sql
2 
-- Determine package versions that depend on a specific package name
 2 
-- Determine package versions that depend on a specific package name
3 
-- Provide the package version  id and version text
 3 
-- Provide the package version id and version text
- 
 
 4 
--  :PKG_ID   - Need packages that match this package ID
- 
 
 5 
--  :PV_ID    - Base package. Provides v_ext and possibly exact match pv_id
- 
 
 6 
--  :MATCH   0 == Don't match exact PV_ID
- 
 
 7 
select pkg.pkg_name,
- 
 
 8 
       pkg.pkg_id ,
4 
SELECT DISTINCT
 9 
       ppv.pv_id,
- 
 
 10 
       ppv.pkg_version,
- 
 
 11 
       pv.pkg_version as used_version,
- 
 
 12 
       pv.pv_id as used_pv_id,
- 
 
 13 
       ppv.DLOCKED,
- 
 
 14 
       ppv.MODIFIER_ID,
- 
 
 15 
       ppv.MODIFIED_STAMP,
- 
 
 16 
       usr.FULL_NAME,
- 
 
 17 
       usr.USER_EMAIL,
5 
       pkg.pkg_name, pv.pv_id, pv.PKG_VERSION, pv.DLOCKED, pv.MODIFIER_ID, pv.MODIFIED_STAMP, usr.FULL_NAME, usr.USER_EMAIL
 18 
       CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
6 
 FROM
19 
  from PACKAGE_DEPENDENCIES dep,
7 
       package_dependencies dep,
 20 
  package_versions pv,
8 
       package_versions pv,
 21 
  package_versions ppv,
9 
       packages pkg,
 22 
  packages pkg,
10 
       USERS usr
 23 
  USERS usr
11 
 WHERE dep.pv_id = pv.pv_id
 24 
  Where dep.DPV_ID = pv.pv_id
12 
   AND dep.dpv_id = :PV_ID
 25 
  AND dep.PV_ID = ppv.pv_id
13 
   AND pv.PKG_ID = :PKG_ID
 26 
  AND ppv.PKG_ID = :PKG_ID
- 
 
 27 
  AND pv.PKG_ID in ( select pkg_id from package_versions where pv_id = :PV_ID )
- 
 
 28 
  AND NVL(pv.v_ext,'LINK') in ( select NVL(v_ext,'LINK') from package_versions where pv_id = :PV_ID )
14 
   AND pv.pkg_id = pkg.pkg_id
 29 
  AND pkg.pkg_id = ppv.pkg_id
- 
 
 30 
  AND (pv.pv_id = :PV_ID OR :MATCH = 0)
15 
   AND pv.MODIFIER_ID = usr.USER_ID(+)
 31 
  AND pv.MODIFIER_ID = usr.USER_ID(+)
16 
ORDER by UPPER(pv.PKG_VERSION)
32 
  ORDER BY UPPER(pkg.pkg_name), UPPER(ppv.pkg_version) desc
- 
 
 33
- 
 
 34