| 7570 |
dpurdie |
1 |
-- UsedByThisSnapshot.sql
|
|
|
2 |
-- Generate summary data for projects that use this package version
|
|
|
3 |
-- Match control via :MATCH
|
|
|
4 |
-- :MATCH 0 - Match any package name
|
|
|
5 |
-- :MATCH 1 - Match Package name and Project Suffix
|
|
|
6 |
-- :MATCH 2 - Match exactly the package. Name, Version and Suffix
|
|
|
7 |
with
|
|
|
8 |
aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID),
|
|
|
9 |
bb AS (select pkg_id from package_versions where pv_id = :PV_ID)
|
|
|
10 |
SELECT rtp.rtag_name,
|
|
|
11 |
rtp.RTAG_ID,
|
|
|
12 |
COUNT(rtp.RTAG_ID) AS USED_COUNT
|
|
|
13 |
FROM ENVIRONMENT_VIEW ev,
|
|
|
14 |
PACKAGE_VERSIONS pv,
|
|
|
15 |
RELEASE_TAGS rt,
|
|
|
16 |
RELEASE_TAGS rtp,
|
|
|
17 |
PROJECTS proj,
|
|
|
18 |
aa, bb
|
|
|
19 |
WHERE ev.PV_ID = pv.PV_ID
|
|
|
20 |
AND ev.RTAG_ID = rt.RTAG_ID
|
|
|
21 |
AND rtp.PROJ_ID = proj.PROJ_ID
|
|
|
22 |
AND rtp.proj_id = :PROJ_ID
|
|
|
23 |
AND rtp.rtag_id = rt.parent_rtag_id
|
|
|
24 |
AND (ev.PV_ID = :PV_ID OR :MATCH != 2)
|
|
|
25 |
AND (NVL(pv.v_ext,'LINK') in ( aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
|
|
|
26 |
AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
|
|
|
27 |
AND rt.official = 'S'
|
|
|
28 |
GROUP BY rtp.rtag_name, rtp.RTAG_ID
|
|
|
29 |
ORDER BY UPPER(rtp.rtag_name) ASC
|
|
|
30 |
|
|
|
31 |
|