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