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: Missing dependencies from release ------- */
      SELECT DISTINCT 'enum_MSG_MISSING_DEPENDENCIES' AS err_message,
            'dependencies.asp' AS rfile,
             '' AS anchor,
              2 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: Package Information Section - Autobuild with VCS Uncontrolled ------- */
      SELECT DISTINCT 'enum_MSG_AUTOBUILD_PACKAGE_IS_UNCONTROLLED' AS err_message,
             'fixed_issues.asp' AS rfile,
             '#PACKAGE_INFORMATION' AS anchor,
             3 AS priority,
             'ERROR' AS products_msg
        FROM PACKAGE_VERSIONS pv
       WHERE pv.pv_id = :PV_ID
         AND pv.build_type = 'A'
         AND pv.vcs_type_id IN (SELECT vt.vcs_type_id FROM vcs_type vt WHERE vt.tag = 'UC' OR vt.tag = 'CVS')


      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 Length(pv.PV_DESCRIPTION) < 1
               OR pv.PV_OVERVIEW IS NULL
 			   OR Length(pv.PV_OVERVIEW) < 1
               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: Build Std Missing For auto-build package versions ------- */
      SELECT DISTINCT 'enum_MSG_AUTOBUILD_PACKAGE_REQUIRES_BUILD_STD_AND_ENV' AS err_message,
             'fixed_issues.asp' AS rfile,
             '#PACKAGE_INFORMATION' AS anchor,
             13 AS priority,
             'ERROR' AS products_msg
        FROM PACKAGE_VERSIONS pv
       WHERE pv.pv_id = :PV_ID
         AND pv.build_type = 'A'
         AND (pv.bs_id IS NULL OR pv.bs_id = 3)

      UNION

      /* ------- ERROR: Build Env Missing For auto-build package versions ------- */
      SELECT DECODE( pbi_data.cnt, 0, 'enum_MSG_AUTOBUILD_PACKAGE_REQUIRES_BUILD_STD_AND_ENV', NULL) AS err_message,
             'fixed_issues.asp' AS rfile,
             '#PACKAGE_INFORMATION' AS anchor,
             14 AS priority,
             'ERROR' AS products_msg
        FROM PACKAGE_VERSIONS pv,
        ( SELECT COUNT(*) AS cnt FROM PACKAGE_BUILD_INFO pbi WHERE pbi.pv_id = :PV_ID ) pbi_data
       WHERE pv.pv_id = :PV_ID
         AND pv.build_type = 'A'
         AND NOT (pv.bs_id IS NULL OR pv.bs_id = 3)

      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,
               15 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,
             16 AS priority,
             'ERROR' AS products_msg
        FROM UNIT_TESTS ut
       WHERE ut.PV_ID = :PV_ID

      ) errmsg
ORDER BY priority
