Subversion Repositories DevTools

/* Latest Recent Released Products */
SELECT pv.pv_id AS PROD_ID,
           pkg.pkg_name,
           pv.pkg_version,
           DECODE ( pkglist.prod_id,
                                NULL, NULL,
                                'disabled' ) AS used
  FROM PACKAGE_VERSIONS pv,
           PACKAGES pkg,
           (
           
            SELECT lpkg.pv_id, upkg.prod_id
                  FROM (
                                /* Get Distinct list of latest packages */
                                SELECT DISTINCT MAX(pv.PV_ID) OVER ( PARTITION BY pv.pkg_id || pv.v_ext ) AS PV_ID
                                  FROM RELEASE_TAGS rt,
                                           RELEASE_CONTENT rc,
                                           PACKAGE_VERSIONS pv,
                                           (
                                            /* Get all Branches */
                                            SELECT vtw.VTREE_ID
                                                  FROM VTREES_WORLD vtw
                                                 WHERE vtw.WORLD_ID IN ( SELECT vtw.WORLD_ID FROM VTREES_WORLD vtw WHERE vtw.VTREE_ID = :RM_VTREE_FK )
                                                 UNION
                                            SELECT TO_NUMBER( :RM_VTREE_FK ) AS VTREE_ID FROM DUAL
                                           ) wo
                                 WHERE wo.VTREE_ID = rt.VTREE_ID
                                   AND rc.RTAG_ID = rt.RTAG_ID
                                   AND rc.PV_ID = pv.pv_id
                                   AND pv.DLOCKED = 'Y'
                                   AND rc.BASE_VIEW_ID = :RM_PRODUCTS_BASE_VIEW
                                   AND TO_DATE( pv.MODIFIED_STAMP, 'DD-MM-YYYY') > TO_DATE ( SYSDATE - :DAYS_BACK_IN_TIME, 'DD-MM-YYYY' )
                                   AND NVL( pv.V_EXT, 'LINK_A_NULL') NOT IN ( SELECT NVL( pe.ext_name, 'LINK_A_NULL') AS V_EXT  
                                                                                                                            FROM PROJECT_EXTENTIONS pe  
                                                                                                                           WHERE pe.proj_id != :RM_PROJECTS_FK )
                            ) lpkg,
                           (
                            /* Products already used in BOMs */                                                                                                    
                                SELECT DISTINCT osc.PROD_ID
                                  FROM BRANCHES br,
                                           BOMS bo,
                                           BOM_CONTENTS bc,
                                           OPERATING_SYSTEMS os,
                                           OS_CONTENTS osc
                                 WHERE br.BRANCH_ID IN ( SELECT bo.BRANCH_ID FROM BOMS bo WHERE bo.BOM_ID = :BOM_ID )
                                   AND bo.BRANCH_ID = br.BRANCH_ID
                                   AND bc.BOM_ID = bo.BOM_ID
                                   AND bc.NODE_ID = os.NODE_ID
                                   AND osc.OS_ID = os.OS_ID             
                                ) upkg
                 WHERE lpkg.PV_ID = upkg.PROD_ID (+)
                
                

                                                                                                   
                                                                                                                                                                                           
                   
           ) pkglist
 WHERE pv.pkg_id = pkg.pkg_id
   AND pkglist.pv_id = pv.pv_id
ORDER BY UPPER(pkg.pkg_name)