Subversion Repositories DevTools

-- Used by Packages in this release
--      :RTAG_ID
--      :PKG_ID
--      :V_EXT
WITH ENVIRONMENT_VIEW as (
-- Select information from RELEASE_CONTENT, WORK_IN_PROGRESS and PLANNED Tables
--      Do not use the ENVIRONMENT_VIEW from the RM schema as it is now very slow
--      This Select is faster simply because we can select on RTAG_ID
      SELECT 2 AS ENV_AREA,
        rc.PV_ID,
        rc.RTAG_ID,
        rc.BASE_VIEW_ID AS VIEW_ID,
        rc.PKG_STATE,
        rc.DEPRECATED_STATE,
        rc.INSERTOR_ID,
        rc.INSERT_STAMP,
        ' ' AS OPERATION
      FROM RELEASE_CONTENT rc
      WHERE rc.RTAG_ID = :RTAG_ID
    UNION
    SELECT 0 AS ENV_AREA,
        wip.PV_ID,
        wip.RTAG_ID,
        wip.VIEW_ID,
        NULL AS PKG_STATE,
        NULL AS DEPRECATED_STATE,
        NULL AS INSERTOR_ID,
        NULL AS INSERT_STAMP,
        ' ' AS OPERATION
      FROM WORK_IN_PROGRESS wip
      WHERE wip.RTAG_ID = :RTAG_ID
    UNION
    SELECT 1 AS ENV_AREA,
        pl.PV_ID,
        pl.RTAG_ID,
        pl.VIEW_ID,
        NULL AS PKG_STATE,
        NULL AS DEPRECATED_STATE,
        NULL AS INSERTOR_ID,
        NULL AS INSERT_STAMP,
        pl.operation
    FROM PLANNED pl
    WHERE pl.RTAG_ID = :RTAG_ID
)
SELECT DECODE( ev.PKG_STATE, NULL, 0, ev.PKG_STATE ) AS PKG_STATE,
       pv.PV_ID,
       pkg.PKG_NAME,
       pv.PKG_VERSION,
       pv.DLOCKED,
       pv.BUILD_TYPE,
       igw.PV_ID AS IGNORE_WARN,
       igw.IS_PATCH_IGNORE,
       dpv.PV_ID AS DPV_ID,
       dpkg.PKG_NAME AS DPKG_NAME,
       dpv.PKG_VERSION AS DPKG_VERSION,
       ev.ENV_AREA,
       vi.VIEW_NAME,
       usr.FULL_NAME,
       usr.USER_EMAIL,
       pv.MODIFIED_STAMP,
       ev.deprecated_state
  FROM ENVIRONMENT_VIEW ev,
       PACKAGE_DEPENDENCIES dep,
       PACKAGE_VERSIONS dpv,
       PACKAGE_VERSIONS pv,
       PACKAGES pkg,
       PACKAGES dpkg,
       USERS usr,
       VIEWS vi,
       ( 
       
         SELECT iw.PV_ID, iw.DPV_ID, iw.IS_PATCH_IGNORE
           FROM IGNORE_WARNINGS iw 
          WHERE iw.RTAG_ID = :RTAG_ID 
          
       ) igw       
 WHERE ev.RTAG_ID = :RTAG_ID
   AND ev.PV_ID = dep.PV_ID
   AND dep.DPV_ID = dpv.PV_ID
   AND dpv.PKG_ID = :PKG_ID
   AND NVL( dpv.V_EXT, '|LINK_A_NULL|') = NVL( :V_EXT, '|LINK_A_NULL|')
   AND dep.PV_ID = pv.PV_ID
   AND pv.PKG_ID = pkg.PKG_ID
   AND dpv.PKG_ID = dpkg.PKG_ID
   AND ev.VIEW_ID = vi.VIEW_ID 
   AND pv.MODIFIER_ID = usr.USER_ID
   AND igw.PV_ID (+) = dep.PV_ID
   AND igw.DPV_ID (+) = dep.DPV_ID
 ORDER BY UPPER(pkg.PKG_NAME)