Subversion Repositories DevTools

/* Product Versions List */
SELECT qry.*
  FROM (
   
                /* Return Last 10 versions */ 
                SELECT last.*
                  FROM (
                                SELECT pv.pv_id,
                                           pv.pkg_version
                                  FROM PACKAGE_VERSIONS pv
                                 WHERE pv.pkg_id = :PKG_ID
                                   AND (
                                                /* Filter other extentions */
                                                ( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN ( 
                                                                                                                                SELECT pe.EXT_NAME
                                                                                                                                  FROM PROJECT_EXTENTIONS pe,
                                                                                                                                           PROJECTS proj 
                                                                                                                                 WHERE proj.PROJ_ID != pe.PROJ_ID
                                                                                                                                   AND proj.PROJ_ID = :PROJ_ID
                                                                                                                           )   AND  :FILTER_ON = 1 )  OR
                                                ( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' )   AND  :FILTER_ON = 0 ) 
                                            )
                                 ORDER BY pv.PV_ID DESC
                                ) last
                 WHERE ROWNUM <= 5                              
                
                UNION
                
                /* Return Date and Extention filter */           
                SELECT pv.pv_id,
                           pv.pkg_version
                  FROM PACKAGE_VERSIONS pv
                 WHERE pv.pkg_id = :PKG_ID
                   AND (
                                /* Filter other extentions */
                                ( NVL( pv.V_EXT, 'LINK_NULL' ) NOT IN ( 
                                                                                                                SELECT pe.EXT_NAME
                                                                                                                  FROM PROJECT_EXTENTIONS pe,
                                                                                                                           PROJECTS proj 
                                                                                                                 WHERE proj.PROJ_ID != pe.PROJ_ID
                                                                                                                   AND proj.PROJ_ID = :PROJ_ID
                                                                                                           )   AND  :FILTER_ON = 1 )  OR
                                ( NVL( pv.V_EXT, 'LINK_NULL' ) = NVL( pv.V_EXT, 'LINK_NULL' )   AND  :FILTER_ON = 0 ) 
                            )
                        AND (
                                 /* Filter on Date Modified */
                                 ( 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
                                 ( 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 )
                                )       
                
                ) qry
                
ORDER BY UPPER(qry.pkg_version)