Subversion Repositories DevTools

/* Product Versions List */
SELECT distinct 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,
                
                BOM_STATE bms,
                branches br,
                boms,
                os_contents osc,
                bom_contents bc,
                operating_systems os,
                production_bom pm, 
                release_authorisation ra 
                
                
                WHERE bms.state_id = pm.state_id
                AND br.PROJ_ID = :PROJ_ID 
                AND boms.BRANCH_ID = br.BRANCH_ID
                and bms.bom_id = boms.BOM_ID
                and osc.prod_id = qry.pv_id
                and bc.BOM_ID = boms.BOM_ID
                and os.NODE_ID = bc.NODE_ID
                and os.os_id = osc.os_id
                and br.BRANCH_ID = :BRANCH_ID
                and ra.PV_ID = qry.pv_id
                and ra.IS_OFFICIAL = 'Y'
                
                
                
ORDER BY UPPER(qry.pkg_version)