Subversion Repositories DevTools

-- =====================================================
-- UserWip.sql
-- Keep in step with 'WorkInProgressByUser.sql'
--
--   Used by index.asp to list Releases and Package Versions 
--   that the user is working on
-- =====================================================
SELECT pr.PROJ_NAME ||' / '|| rt.RTAG_NAME  AS LOCATION,
       pkg.PKG_NAME,
       rel.PKG_VERSION,
       rel.PV_ID,
       rt.RTAG_ID
  FROM (
  
        /* Get WIP Packages */
        SELECT wip.PV_ID, rt.RTAG_ID, pv.PKG_ID, pv.PKG_VERSION
          FROM WORK_IN_PROGRESS wip,
               RELEASE_TAGS rt,
               PACKAGE_VERSIONS pv
         WHERE wip.RTAG_ID = rt.RTAG_ID
           AND rt.OFFICIAL IN ('N','R','C')
           AND wip.PV_ID = pv.PV_ID
           AND pv.DLOCKED != 'Y'
           AND pv.MODIFIER_ID = :USER_ID
        
        UNION 
           
        /* Get WIP Patches */
        SELECT pp.PATCH_ID AS PV_ID,rt.RTAG_ID,pv.PKG_ID,pv.PKG_VERSION
         FROM RELEASE_CONTENT rc,
              RELEASE_TAGS rt,
              PACKAGE_PATCHES pp,
              PACKAGE_VERSIONS pv,
              WORK_IN_PROGRESS wip
        WHERE rc.RTAG_ID    = rt.RTAG_ID
          AND rt.OFFICIAL    IN ('N','R','C')
          AND pp.PV_ID        = rc.PV_ID
          AND pp.PATCH_ID     = pv.PV_ID
          AND pv.DLOCKED     != 'Y'
          AND pv.IS_OBSOLETE IS NULL
          AND pv.MODIFIER_ID  = :USER_ID
          AND wip.RTAG_ID     = rt.RTAG_ID
          AND wip.PV_ID       = pp.PATCH_ID
               
         
       ) rel,
       PACKAGES pkg,
       RELEASE_TAGS rt,
       PROJECTS pr
 WHERE rel.PKG_ID = pkg.PKG_ID
   AND rel.RTAG_ID = rt.RTAG_ID
   AND rt.PROJ_ID = pr.PROJ_ID 
ORDER BY UPPER(LOCATION), UPPER(pkg.PKG_NAME)