Subversion Repositories DevTools

Rev

Rev 5945 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
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
5947 dpurdie 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)
5945 dpurdie 11 
SELECT DISTINCT SDK_NAME,
12 
        st.sdk_state,
13 
        st.SDK_ID,
14 
        st.SDKTAG_NAME,
15 
        st.SDKTAG_ID,
16 
        pkg.pkg_name,
17 
        pv.PKG_VERSION,
18 
        pv.pv_id,
19 
        pv.MODIFIER_ID,
20 
        pv.MODIFIED_STAMP,
21 
        usr.FULL_NAME,
22 
        usr.USER_EMAIL,
23 
        CASE pv.pv_id WHEN TO_NUMBER(:PV_ID) THEN 1 ELSE 0 END as MATCH
24 
FROM SDK_CONTENT sc,
25 
  SDK_TAGS st,
26 
  SDK_NAMES sn,
27 
  PACKAGE_VERSIONS pv,
28 
  PACKAGES pkg,
5947 dpurdie 29 
  USERS usr,
30 
  aa,bb
5945 dpurdie 31 
WHERE sc.SDKTAG_ID = st.SDKTAG_ID
32 
  AND (sc.PV_ID  = :PV_ID OR :MATCH != 2)
5947 dpurdie 33 
  AND (NVL(pv.v_ext,'LINK') in (  aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
34 
  AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
5945 dpurdie 35 
  AND st.SDK_ID = sn.SDK_ID
36 
  AND sc.PV_ID = pv.PV_ID
37 
  AND st.SDK_ID = :SDK_ID
38 
  AND pv.pkg_id = pkg.pkg_id
39 
  AND pv.MODIFIER_ID = usr.USER_ID(+)
40 
ORDER BY UPPER(SDK_NAME), st.SDKTAG_ID