| 13 |
rsolanki |
1 |
/* Product Versions List */
|
|
|
2 |
SELECT qry.*
|
|
|
3 |
FROM (
|
|
|
4 |
|
|
|
5 |
/* Return Last 10 versions */
|
|
|
6 |
SELECT last.*
|
|
|
7 |
FROM (
|
|
|
8 |
SELECT pv.pv_id,
|
|
|
9 |
pv.pkg_version
|
|
|
10 |
FROM PACKAGE_VERSIONS pv
|
|
|
11 |
WHERE pv.pkg_id = :PKG_ID
|
|
|
12 |
AND (
|
|
|
13 |
/* Filter other extentions */
|
|
|
14 |
( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN (
|
|
|
15 |
SELECT pe.EXT_NAME
|
|
|
16 |
FROM PROJECT_EXTENTIONS pe,
|
|
|
17 |
PROJECTS proj
|
|
|
18 |
WHERE proj.PROJ_ID != pe.PROJ_ID
|
|
|
19 |
AND proj.PROJ_ID = :PROJ_ID
|
|
|
20 |
) AND :FILTER_ON = 1 ) OR
|
|
|
21 |
( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' ) AND :FILTER_ON = 0 )
|
|
|
22 |
)
|
|
|
23 |
ORDER BY pv.PV_ID DESC
|
|
|
24 |
) last
|
|
|
25 |
WHERE ROWNUM <= 5
|
|
|
26 |
|
|
|
27 |
UNION
|
|
|
28 |
|
|
|
29 |
/* Return Date and Extention filter */
|
|
|
30 |
SELECT pv.pv_id,
|
|
|
31 |
pv.pkg_version
|
|
|
32 |
FROM PACKAGE_VERSIONS pv
|
|
|
33 |
WHERE pv.pkg_id = :PKG_ID
|
|
|
34 |
AND (
|
|
|
35 |
/* Filter other extentions */
|
|
|
36 |
( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN (
|
|
|
37 |
SELECT pe.EXT_NAME
|
|
|
38 |
FROM PROJECT_EXTENTIONS pe,
|
|
|
39 |
PROJECTS proj
|
|
|
40 |
WHERE proj.PROJ_ID != pe.PROJ_ID
|
|
|
41 |
AND proj.PROJ_ID = :PROJ_ID
|
|
|
42 |
) AND :FILTER_ON = 1 ) OR
|
|
|
43 |
( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' ) AND :FILTER_ON = 0 )
|
|
|
44 |
)
|
|
|
45 |
AND (
|
|
|
46 |
/* Filter on Date Modified */
|
|
|
47 |
( 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
|
|
|
48 |
( 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 )
|
|
|
49 |
)
|
|
|
50 |
|
|
|
51 |
) qry
|
|
|
52 |
|
|
|
53 |
ORDER BY UPPER(qry.pkg_version)
|