Subversion Repositories DevTools

SELECT *
  FROM  (

        /* ------- ERROR: Package is unbuildable ------- */
        SELECT DISTINCT 'enum_MSG_UNBUILDABLE_PACKAGE' AS err_message,
               'fixed_issues.asp' AS rfile,
               '#PACKAGE_INFORMATION' AS anchor,
               1 AS priority,
               'ERROR' AS products_msg
         FROM  PACKAGE_VERSIONS pv
         WHERE pv.pv_id = :PV_ID
           AND pv.build_type  = 'U'

        UNION

        /* ------- 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 (
                -- Get all the dependent package-versions
                SELECT TO_NUMBER(:RTAG_ID) AS RTAG_ID, dep.PV_ID, dep.DPV_ID
                       FROM PACKAGE_DEPENDENCIES dep
                      WHERE dep.PV_ID = :PV_ID
                -- Remove those that are in the release                
                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
                -- Remove those that have ignore warnings flagged                
                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
                -- Remove those that are marked as advisory ripple
                MINUS                  
                    SELECT TO_NUMBER(:RTAG_ID) AS RTAG_ID, dep.PV_ID, dep.DPV_ID
                             FROM   PACKAGE_DEPENDENCIES dep,
                                    PACKAGE_VERSIONS PV
                            WHERE   dep.PV_ID = :PV_ID
                             and    dep.DPV_ID = PV.PV_ID
                             and    PV.PKG_ID
                              in    (
                                -- Select PKG_IDs of those packages that are marked as advisory
                                SELECT  PV.PKG_ID
                                from    ADVISORY_RIPPLE AR,
                                        PACKAGE_VERSIONS PV
                                where   AR.RTAG_ID = :RTAG_ID
                                and     AR.PV_ID = PV.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,
               3 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,
               4 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.PV_DESCRIPTION IS NULL
                 OR pv.PV_OVERVIEW IS NULL
                 OR pv.IS_BUILD_ENV_REQUIRED IS NULL
               )

        UNION

        /* ------- ERROR: Version Control Info ------- */
        SELECT DISTINCT 'enum_MSG_VCS_INFORMATION_INCOMPLETE' AS err_message,
               'fixed_issues.asp' AS rfile,
               '#PACKAGE_INFORMATION' AS anchor,
               11 AS priority,
               'ERROR' AS products_msg
         FROM  PACKAGE_VERSIONS pv,
               VCS_TYPE vt
         WHERE pv.pv_id = :PV_ID
           AND pv.vcs_type_id  = vt.vcs_type_id
                   AND ( vt.tag != 'UC' AND ( pv.PKG_LABEL IS NULL OR pv.SRC_PATH IS NULL ) )

        UNION

        /* ------- ERROR: Subversion to CC regression or GIT to Subversion regression ------- */
        SELECT DISTINCT 'enum_MSG_MIXED_VCS' AS err_message,
               'fixed_issues.asp' AS rfile,
               '#PACKAGE_INFORMATION' AS anchor,
               12 AS priority,
               'ERROR' AS products_msg
         FROM  PACKAGE_VERSIONS pv,
               PACKAGES pkg,
               VCS_TYPE vt
         WHERE pv.pv_id = :PV_ID
           AND pv.pkg_id = pkg.pkg_id
           AND pv.vcs_type_id  = vt.vcs_type_id
           AND PK_RMAPI.has_mixed_vcs(vt.tag, pkg.pkg_name) > 0

        UNION
        
        /* ------- ERROR: Manually Versioned SVN Packages must have PEG ------- */
        SELECT DISTINCT 'enum_MSG_VCS_SVN_NOT_PEGGED' AS err_message,
               'fixed_issues.asp' AS rfile,
               '#PACKAGE_INFORMATION' AS anchor,
               13 AS priority,
               'ERROR' AS products_msg
         FROM  PACKAGE_VERSIONS pv,
               VCS_TYPE vt
         WHERE pv.pv_id = :PV_ID
           AND pv.vcs_type_id  = vt.vcs_type_id
           AND pv.build_type = 'M'
           AND vt.tag = 'SVN'
           AND NOT REGEXP_LIKE( pv.PKG_LABEL, '@[0-9]+$')
        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,
               14 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,
               15 AS priority,
               'ERROR' AS products_msg
          FROM UNIT_TESTS ut
         WHERE ut.PV_ID = :PV_ID


        ) errmsg
ORDER BY priority