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)