Subversion Repositories DevTools

Rev

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

Rev Author Line No. Line
6123 dpurdie 1 
/* rep_obsolete_packages.sql */
119 ghuddy 2 
SELECT frc.pv_id, pkg.pkg_name, frc.pkg_version, frc.view_name, frc.view_id,
6123 dpurdie 3 
       frc.modifier, frc.modifier_email, frc.MODIFIED_STAMP,
4 
       frc.insertor, frc.insertor_email, frc.INSERT_STAMP
119 ghuddy 5 
  FROM
6123 dpurdie 6 
        (
7 
        /* All released (locked) packages from a release MINUS
8 
            Products and Auto_Products  Bit-0 of SMODE
9 
            3RDPARTY_PRODUCTS           Bit-1 of SMODE
10 
            Those marked as deployable  Bit-2 of SMODE
11 
            Those imported via an SDK
12 
        */
13 
        SELECT pv.pkg_id, pv.v_ext
14 
          FROM release_content rc,
15 
               package_versions pv
16 
         WHERE rc.rtag_id = :RTAG_ID
17 
           AND ( NOT rc.base_view_id = 5    OR ( BITAND (:SMODE, 1) = 0) )
18 
           AND ( NOT rc.base_view_id = 2602 OR ( BITAND (:SMODE, 1) = 0) )
19 
           AND ( NOT rc.base_view_id = 2381 OR ( BITAND (:SMODE, 2) = 0) )
20 
           AND ( pv.is_deployable is NULL   OR ( BITAND (:SMODE, 4) = 0) )
21 
           AND pv.dlocked = 'Y'
22 
           AND rc.pv_id = pv.pv_id
23 
         AND rc.SDKTAG_ID IS NULL
24 
        MINUS
25 
        /* All used by from a release */
26 
        SELECT dpv.pkg_id, dpv.v_ext
27 
          FROM release_content rc,
28 
               package_dependencies dep,
29 
            package_versions dpv
30 
         WHERE rc.rtag_id = :RTAG_ID
31 
           AND rc.pv_id = dep.pv_id
32 
           AND dep.dpv_id = dpv.pv_id
33 
        ) nou,
34 
        (
35 
        /* Full release content */
36 
        SELECT pv.pv_id, pv.pkg_id, pv.v_ext, pv.pkg_version, vi.view_name, vi.view_id,
37 
               lmusr.full_name AS modifier, lmusr.user_email AS modifier_email, pv.MODIFIED_STAMP,
38 
               arusr.full_name AS insertor, arusr.user_email AS insertor_email, rc.INSERT_STAMP
39 
          FROM release_content rc,
40 
               package_versions pv,
41 
               views vi,
42 
               users lmusr,
43 
               users arusr
44 
         WHERE rc.rtag_id = :RTAG_ID
45 
           AND rc.pv_id = pv.pv_id
46 
           AND rc.base_view_id = vi.view_id
47 
           AND pv.MODIFIER_ID = lmusr.user_id
48 
           AND rc.INSERTOR_ID = arusr.user_id
49 
        ) frc,
50 
        packages pkg
119 ghuddy 51 
WHERE nou.pkg_id = frc.pkg_id
6123 dpurdie 52 
  AND NVL(nou.v_ext, 'LINK_A_NULL') = NVL(frc.v_ext, 'LINK_A_NULL')
119 ghuddy 53 
  AND frc.pkg_id = pkg.pkg_id
6123 dpurdie 54 
ORDER BY UPPER(frc.view_name), UPPER(pkg.pkg_name)