| 64 |
jtweddle |
1 |
/* Latest Recent Released Products */
|
|
|
2 |
SELECT pv.pv_id AS PROD_ID,
|
|
|
3 |
pkg.pkg_name,
|
|
|
4 |
pv.pkg_version,
|
|
|
5 |
DECODE ( pkglist.prod_id,
|
|
|
6 |
NULL, NULL,
|
|
|
7 |
'disabled' ) AS used
|
|
|
8 |
FROM PACKAGE_VERSIONS pv,
|
|
|
9 |
PACKAGES pkg,
|
|
|
10 |
(
|
|
|
11 |
|
|
|
12 |
SELECT lpkg.pv_id, upkg.prod_id
|
|
|
13 |
FROM (
|
|
|
14 |
/* Get Distinct list of latest packages */
|
|
|
15 |
SELECT DISTINCT MAX(pv.PV_ID) OVER ( PARTITION BY pv.pkg_id || pv.v_ext ) AS PV_ID
|
|
|
16 |
FROM RELEASE_TAGS rt,
|
|
|
17 |
RELEASE_CONTENT rc,
|
|
|
18 |
PACKAGE_VERSIONS pv,
|
|
|
19 |
(
|
|
|
20 |
/* Get all Branches */
|
|
|
21 |
SELECT vtw.VTREE_ID
|
|
|
22 |
FROM VTREES_WORLD vtw
|
|
|
23 |
WHERE vtw.WORLD_ID IN ( SELECT vtw.WORLD_ID FROM VTREES_WORLD vtw WHERE vtw.VTREE_ID = :RM_VTREE_FK )
|
|
|
24 |
UNION
|
|
|
25 |
SELECT TO_NUMBER( :RM_VTREE_FK ) AS VTREE_ID FROM DUAL
|
|
|
26 |
) wo
|
|
|
27 |
WHERE wo.VTREE_ID = rt.VTREE_ID
|
|
|
28 |
AND rc.RTAG_ID = rt.RTAG_ID
|
|
|
29 |
AND rc.PV_ID = pv.pv_id
|
|
|
30 |
AND pv.DLOCKED = 'Y'
|
|
|
31 |
AND rc.BASE_VIEW_ID = :RM_PRODUCTS_BASE_VIEW
|
|
|
32 |
AND TO_DATE( pv.MODIFIED_STAMP, 'DD-MM-YYYY') > TO_DATE ( SYSDATE - :DAYS_BACK_IN_TIME, 'DD-MM-YYYY' )
|
|
|
33 |
AND NVL( pv.V_EXT, 'LINK_A_NULL') NOT IN ( SELECT NVL( pe.ext_name, 'LINK_A_NULL') AS V_EXT
|
|
|
34 |
FROM PROJECT_EXTENTIONS pe
|
|
|
35 |
WHERE pe.proj_id != :RM_PROJECTS_FK )
|
|
|
36 |
) lpkg,
|
|
|
37 |
(
|
|
|
38 |
/* Products already used in BOMs */
|
|
|
39 |
SELECT DISTINCT osc.PROD_ID
|
|
|
40 |
FROM BRANCHES br,
|
|
|
41 |
BOMS bo,
|
|
|
42 |
BOM_CONTENTS bc,
|
|
|
43 |
OPERATING_SYSTEMS os,
|
|
|
44 |
OS_CONTENTS osc
|
|
|
45 |
WHERE br.BRANCH_ID IN ( SELECT bo.BRANCH_ID FROM BOMS bo WHERE bo.BOM_ID = :BOM_ID )
|
|
|
46 |
AND bo.BRANCH_ID = br.BRANCH_ID
|
|
|
47 |
AND bc.BOM_ID = bo.BOM_ID
|
|
|
48 |
AND bc.NODE_ID = os.NODE_ID
|
|
|
49 |
AND osc.OS_ID = os.OS_ID
|
|
|
50 |
) upkg
|
|
|
51 |
WHERE lpkg.PV_ID = upkg.PROD_ID (+)
|
|
|
52 |
|
|
|
53 |
|
|
|
54 |
|
|
|
55 |
|
|
|
56 |
|
|
|
57 |
|
|
|
58 |
) pkglist
|
|
|
59 |
WHERE pv.pkg_id = pkg.pkg_id
|
|
|
60 |
AND pkglist.pv_id = pv.pv_id
|
|
|
61 |
ORDER BY UPPER(pkg.pkg_name)
|
|
|
62 |
|
|
|
63 |
|