Subversion Repositories DevTools

SELECT *
  FROM  (

                /* ------- ERROR: Broken dependencies found ------- */
                SELECT DISTINCT 'enum_MSG_BROKEN_DEPENDENCIES_FOUND' AS err_message,
                           'dependencies.asp' AS rfile,
                       '' AS anchor,
               2 AS priority,
                           'WARNING' AS products_msg
                  FROM (
                           SELECT TO_NUMBER(:RTAG_ID) AS RTAG_ID, dep.PV_ID, dep.DPV_ID
                             FROM PACKAGE_DEPENDENCIES dep
                                WHERE dep.PV_ID = :PV_ID
                            MINUS
                           SELECT rc.RTAG_ID, TO_NUMBER(:PV_ID) AS PV_ID, rc.PV_ID AS DPV_ID
                             FROM RELEASE_CONTENT rc
                                WHERE rc.RTAG_ID = :RTAG_ID
                        MINUS
                       SELECT igw.rtag_id, igw.pv_id, igw.dpv_id
                         FROM IGNORE_WARNINGS igw
                        WHERE igw.rtag_id = :RTAG_ID
                          AND igw.pv_id = :PV_ID
                        ) broken_dep

                UNION

                /* ------- ERROR: Missing dependencies from release ------- */
                SELECT DISTINCT 'enum_MSG_MISSING_DEPENDENCIES' AS err_message,
                           'dependencies.asp' AS rfile,
                       '' AS anchor,
               1 AS priority,
                           'ERROR' AS products_msg
                  FROM PACKAGE_DEPENDENCIES dep,
                       PACKAGE_VERSIONS dpv
                 WHERE dep.pv_id = :PV_ID
                   AND dep.dpv_id = dpv.pv_id
                   AND (dpv.pkg_id, dpv.v_ext) NOT IN
                       (
                        SELECT pv.pkg_id, pv.v_ext
                          FROM RELEASE_CONTENT rel,
                               PACKAGE_VERSIONS pv
                         WHERE rel.rtag_id = :RTAG_ID
                           AND rel.pv_id = pv.pv_id
                       )

                UNION

                /* ------- ERROR: Unofficial dependencies found ------- */
                SELECT DISTINCT 'enum_MSG_UNOFFICIAL_DEPENDENCIES_FOUND' AS err_message,
                           'dependencies.asp' AS rfile,
                       '' AS anchor,
               3 AS priority,
                           'ERROR' AS products_msg
                  FROM PACKAGE_DEPENDENCIES dep,
                       PACKAGE_VERSIONS dpv
                 WHERE dep.dpv_id = dpv.pv_id
                   AND dep.pv_id = :PV_ID
                   AND dpv.dlocked = 'N'

                UNION

                /* ------- ERROR: Package Information Section Incomplete ------- */
                SELECT DISTINCT 'enum_MSG_PACKAGE_INFORMATION_INCOMPLETE' AS err_message,
                           'fixed_issues.asp' AS rfile,
                       '#PACKAGE_INFORMATION' AS anchor,
               10 AS priority,
                           'ERROR' AS products_msg
                  FROM PACKAGE_VERSIONS pv
                 WHERE pv.pv_id = :PV_ID
         AND (     pv.SRC_PATH IS NULL
                                 OR pv.PV_DESCRIPTION IS NULL
                                 OR pv.PV_OVERVIEW IS NULL
                                 OR pv.IS_BUILD_ENV_REQUIRED IS NULL
                       )

                UNION

        /* ------- ERROR: Reason For This Version ------- */
        SELECT DECODE( pv.COMMENTS,
                                   NULL, DECODE( fixedcq.cnt, 0, 'enum_MSG_REASON_FOR_THIS_VERSION_NOT_SUPLIED', NULL ), NULL ) AS err_message,
               'fixed_issues.asp' AS rfile,
                       '#REASON_FOR_THIS_RELEASE' AS anchor,
               11 AS priority,
                           'ERROR' AS products_msg
                  FROM (
                        SELECT COUNT(*) AS cnt
                          FROM CQ_ISSUES cqi
                         WHERE cqi.pv_id = :PV_ID
                           AND iss_state = :enumISSUES_STATE_FIXED
                        ) fixedcq,
                PACKAGE_VERSIONS pv
                 WHERE pv.pv_id = :PV_ID

                UNION

                /* ------- ERROR: Unit Test Section Incompete ------- */
                SELECT DECODE( COUNT(*), 0, 'enum_MSG_UNIT_TESTS_NOT_SUPPLIED', NULL ) AS err_message,
                           'fixed_issues.asp' AS rfile,
                       '#UNIT_TESTS' AS anchor,
               12 AS priority,
                           'ERROR' AS products_msg
                  FROM UNIT_TESTS ut
                 WHERE ut.PV_ID = :PV_ID

                ) errmsg
ORDER BY priority