| 5945 |
dpurdie |
1 |
-- UsedBySDKDetail.sql
|
|
|
2 |
-- Locate package versions that use the package specified by :PV_ID
|
|
|
3 |
-- Within an SDK specified by :SDK_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 |
SELECT DISTINCT SDK_NAME,
|
|
|
9 |
st.sdk_state,
|
|
|
10 |
st.SDK_ID,
|
|
|
11 |
st.SDKTAG_NAME,
|
|
|
12 |
st.SDKTAG_ID,
|
|
|
13 |
pkg.pkg_name,
|
|
|
14 |
pv.PKG_VERSION,
|
|
|
15 |
pv.pv_id,
|
|
|
16 |
pv.MODIFIER_ID,
|
|
|
17 |
pv.MODIFIED_STAMP,
|
|
|
18 |
usr.FULL_NAME,
|
|
|
19 |
usr.USER_EMAIL,
|
|
|
20 |
CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
|
|
|
21 |
FROM SDK_CONTENT sc,
|
|
|
22 |
SDK_TAGS st,
|
|
|
23 |
SDK_NAMES sn,
|
|
|
24 |
PACKAGE_VERSIONS pv,
|
|
|
25 |
PACKAGES pkg,
|
|
|
26 |
USERS usr
|
|
|
27 |
WHERE sc.SDKTAG_ID = st.SDKTAG_ID
|
|
|
28 |
AND (sc.PV_ID = :PV_ID OR :MATCH != 2)
|
|
|
29 |
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)
|
|
|
30 |
AND (pv.PKG_ID in ( select pkg_id from package_versions where pv_id = :PV_ID) OR :MATCH = 2)
|
|
|
31 |
AND st.SDK_ID = sn.SDK_ID
|
|
|
32 |
AND sc.PV_ID = pv.PV_ID
|
|
|
33 |
AND st.SDK_ID = :SDK_ID
|
|
|
34 |
AND pv.pkg_id = pkg.pkg_id
|
|
|
35 |
AND pv.MODIFIER_ID = usr.USER_ID(+)
|
|
|
36 |
ORDER BY UPPER(SDK_NAME), st.SDKTAG_ID
|