| Line 5... |
Line 5... |
| 5 |
-- :PV_ID - Base package. Provides v_ext and possibly exact match pv_id
|
5 |
-- :PV_ID - Base package. Provides v_ext and possibly exact match pv_id
|
| 6 |
-- Match control via :MATCH
|
6 |
-- Match control via :MATCH
|
| 7 |
-- :MATCH 0 - Match any package name
|
7 |
-- :MATCH 0 - Match any package name
|
| 8 |
-- :MATCH 1 - Match Package name and Project Suffix
|
8 |
-- :MATCH 1 - Match Package name and Project Suffix
|
| 9 |
-- :MATCH 2 - Match exactly the package. Name, Version and Suffix
|
9 |
-- :MATCH 2 - Match exactly the package. Name, Version and Suffix
|
| - |
|
10 |
with
|
| - |
|
11 |
aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID),
|
| - |
|
12 |
bb AS (select pkg_id from package_versions where pv_id = :PV_ID)
|
| 10 |
select pkg.pkg_name,
|
13 |
select pkg.pkg_name,
|
| 11 |
pkg.pkg_id ,
|
14 |
pkg.pkg_id ,
|
| 12 |
ppv.pv_id,
|
15 |
ppv.pv_id,
|
| 13 |
ppv.pkg_version,
|
16 |
ppv.pkg_version,
|
| 14 |
pv.pkg_version as used_version,
|
17 |
pv.pkg_version as used_version,
|
| Line 21... |
Line 24... |
| 21 |
CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
|
24 |
CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
|
| 22 |
from PACKAGE_DEPENDENCIES dep,
|
25 |
from PACKAGE_DEPENDENCIES dep,
|
| 23 |
package_versions pv,
|
26 |
package_versions pv,
|
| 24 |
package_versions ppv,
|
27 |
package_versions ppv,
|
| 25 |
packages pkg,
|
28 |
packages pkg,
|
| 26 |
USERS usr
|
29 |
USERS usr,
|
| - |
|
30 |
aa, bb
|
| 27 |
Where dep.DPV_ID = pv.pv_id
|
31 |
Where dep.DPV_ID = pv.pv_id
|
| 28 |
AND dep.PV_ID = ppv.pv_id
|
32 |
AND dep.PV_ID = ppv.pv_id
|
| 29 |
AND ppv.PKG_ID = :PKG_ID
|
33 |
AND ppv.PKG_ID = :PKG_ID
|
| 30 |
AND pkg.pkg_id = ppv.pkg_id
|
34 |
AND pkg.pkg_id = ppv.pkg_id
|
| 31 |
AND pv.MODIFIER_ID = usr.USER_ID(+)
|
35 |
AND pv.MODIFIER_ID = usr.USER_ID(+)
|
| 32 |
AND (pv.PV_ID = :PV_ID OR :MATCH != 2)
|
36 |
AND (pv.PV_ID = :PV_ID OR :MATCH != 2)
|
| 33 |
AND (NVL(pv.v_ext,'LINK') in ( select NVL(v_ext,'LINK') from package_versions where pv_id = :PV_ID ) OR :MATCH = 2 OR :MATCH = 0)
|
37 |
AND (NVL(pv.v_ext,'LINK') in ( aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
|
| 34 |
AND (pv.PKG_ID in ( select pkg_id from package_versions where pv_id = :PV_ID) OR :MATCH = 2)
|
38 |
AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
|
| 35 |
ORDER BY UPPER(pkg.pkg_name), UPPER(ppv.pkg_version) desc
|
39 |
ORDER BY UPPER(pkg.pkg_name), UPPER(ppv.pkg_version) desc
|