| 7570 |
dpurdie |
1 |
-- UsedByThisSnapshotDetail.sql
|
|
|
2 |
-- Generate detailed data for projects that use this package version
|
|
|
3 |
-- Within an PARENT RELEASE specified by :RTAG_ID
|
|
|
4 |
-- Match control via :MATCH
|
|
|
5 |
-- :MATCH 0 - Match any package name
|
|
|
6 |
-- :MATCH 1 - Match Package name and Project Suffix
|
|
|
7 |
-- :MATCH 2 - Match exactly the package. Name, Version and Suffix
|
|
|
8 |
with
|
|
|
9 |
aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID),
|
|
|
10 |
bb AS (select pkg_id from package_versions where pv_id = :PV_ID)
|
|
|
11 |
SELECT DECODE( ev.PKG_STATE, NULL, 0, ev.PKG_STATE ) AS PKG_STATE,
|
|
|
12 |
rt.RTAG_ID,
|
|
|
13 |
rt.RTAG_NAME,
|
|
|
14 |
TO_CHAR(rt.created_stamp,'DD-Mon-YYYY HH24:MI:SS') AS created_stamp,
|
|
|
15 |
rt.OFFICIAL,
|
|
|
16 |
ev.ENV_AREA,
|
|
|
17 |
pv.PV_ID,
|
|
|
18 |
pkg.PKG_NAME,
|
|
|
19 |
pv.PKG_VERSION,
|
|
|
20 |
pv.DLOCKED,
|
|
|
21 |
usr.FULL_NAME,
|
|
|
22 |
usr.USER_EMAIL,
|
|
|
23 |
pv.MODIFIED_STAMP
|
|
|
24 |
FROM ENVIRONMENT_VIEW ev,
|
|
|
25 |
PACKAGE_VERSIONS pv,
|
|
|
26 |
PACKAGES pkg,
|
|
|
27 |
RELEASE_TAGS rt,
|
|
|
28 |
USERS usr,
|
|
|
29 |
aa,bb
|
|
|
30 |
WHERE ev.PV_ID = pv.PV_ID
|
|
|
31 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
32 |
AND ev.RTAG_ID = rt.RTAG_ID
|
|
|
33 |
AND pv.MODIFIER_ID = usr.USER_ID
|
|
|
34 |
AND rt.PARENT_RTAG_ID = :RTAG_ID
|
|
|
35 |
AND (ev.PV_ID = :PV_ID OR :MATCH != 2)
|
|
|
36 |
AND (NVL(pv.v_ext,'LINK') in ( aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
|
|
|
37 |
AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
|
|
|
38 |
AND rt.official = 'S'
|
|
|
39 |
ORDER BY rt.created_stamp DESC
|
|
|
40 |
|