| 64 |
jtweddle |
1 |
/* Product Versions List */
|
|
|
2 |
SELECT distinct qry.*
|
|
|
3 |
FROM (
|
|
|
4 |
|
|
|
5 |
/* Return Last 10 versions */
|
|
|
6 |
SELECT last.*
|
|
|
7 |
FROM (
|
|
|
8 |
SELECT pv.pv_id,
|
|
|
9 |
pv.pkg_version,
|
|
|
10 |
DECODE ( pv.pv_id,
|
|
|
11 |
:OLD_PROD_ID, 'selected',
|
|
|
12 |
NULL ) AS selected
|
|
|
13 |
FROM PACKAGE_VERSIONS pv
|
|
|
14 |
WHERE pv.pkg_id = :PKG_ID
|
|
|
15 |
AND (
|
|
|
16 |
/* Filter other extentions */
|
|
|
17 |
( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN (
|
|
|
18 |
SELECT pe.EXT_NAME
|
|
|
19 |
FROM PROJECT_EXTENTIONS pe,
|
|
|
20 |
PROJECTS proj
|
|
|
21 |
WHERE proj.PROJ_ID != pe.PROJ_ID
|
|
|
22 |
AND proj.PROJ_ID = :PROJ_ID
|
|
|
23 |
) AND :FILTER_ON = 1 ) OR
|
|
|
24 |
( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' ) AND :FILTER_ON = 0 )
|
|
|
25 |
)
|
|
|
26 |
ORDER BY pv.PV_ID DESC
|
|
|
27 |
) last
|
|
|
28 |
WHERE ROWNUM <= 5
|
|
|
29 |
|
|
|
30 |
UNION
|
|
|
31 |
|
|
|
32 |
/* Return Date and Extention filter */
|
|
|
33 |
SELECT pv.pv_id,
|
|
|
34 |
pv.pkg_version,
|
|
|
35 |
DECODE ( pv.pv_id,
|
|
|
36 |
:OLD_PROD_ID, 'selected',
|
|
|
37 |
NULL ) AS selected
|
|
|
38 |
FROM PACKAGE_VERSIONS pv
|
|
|
39 |
WHERE pv.pkg_id = :PKG_ID
|
|
|
40 |
AND (
|
|
|
41 |
/* Filter other extentions */
|
|
|
42 |
( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN (
|
|
|
43 |
SELECT pe.EXT_NAME
|
|
|
44 |
FROM PROJECT_EXTENTIONS pe,
|
|
|
45 |
PROJECTS proj
|
|
|
46 |
WHERE proj.PROJ_ID != pe.PROJ_ID
|
|
|
47 |
AND proj.PROJ_ID = :PROJ_ID
|
|
|
48 |
) AND :FILTER_ON = 1 ) OR
|
|
|
49 |
( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' ) AND :FILTER_ON = 0 )
|
|
|
50 |
)
|
|
|
51 |
AND (
|
|
|
52 |
/* Filter on Date Modified */
|
|
|
53 |
( TO_DATE( TO_CHAR( pv.MODIFIED_STAMP,'DD-MON-YYYY' ),'DD-MON-YYYY' ) >= TO_DATE( TO_CHAR( SYSDATE - 365/2,'DD-MON-YYYY' ),'DD-MON-YYYY' ) AND :FILTER_ON = 1 ) OR
|
|
|
54 |
( TO_DATE( TO_CHAR( pv.MODIFIED_STAMP,'DD-MON-YYYY' ),'DD-MON-YYYY' ) = TO_DATE( TO_CHAR( pv.MODIFIED_STAMP,'DD-MON-YYYY' ),'DD-MON-YYYY' ) AND :FILTER_ON = 0 )
|
|
|
55 |
)
|
|
|
56 |
|
|
|
57 |
) qry,
|
|
|
58 |
|
|
|
59 |
BOM_STATE bms,
|
|
|
60 |
branches br,
|
|
|
61 |
boms,
|
|
|
62 |
os_contents osc,
|
|
|
63 |
bom_contents bc,
|
|
|
64 |
operating_systems os,
|
|
|
65 |
production_bom pm
|
|
|
66 |
|
|
|
67 |
|
|
|
68 |
WHERE bms.state_id = pm.state_id
|
|
|
69 |
AND br.PROJ_ID = :PROJ_ID
|
|
|
70 |
AND boms.BRANCH_ID = br.BRANCH_ID
|
|
|
71 |
and bms.bom_id = boms.BOM_ID
|
|
|
72 |
and osc.prod_id = qry.pv_id
|
|
|
73 |
and bc.BOM_ID = boms.BOM_ID
|
|
|
74 |
and os.NODE_ID = bc.NODE_ID
|
|
|
75 |
and os.os_id = osc.os_id
|
|
|
76 |
and br.BRANCH_ID = :BRANCH_ID
|
|
|
77 |
|
|
|
78 |
|
|
|
79 |
|
|
|
80 |
ORDER BY UPPER(qry.pkg_version)
|