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 (
				-- 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,
               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.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 = 'SVN' OR vt.tag = 'CC' or vt.tag = 'CVS')
				   AND (    pv.PKG_LABEL IS NULL
		                 OR pv.SRC_PATH IS NULL
						)
				)
		UNION

		/* ------- ERROR: Subversion to CC regression ------- */
		SELECT DISTINCT 'enum_MSG_VCS_SUBVERSION_CC_MIX' 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 = 'CC' or vt.tag = 'CVS' )
		   AND ( PK_RMAPI.IsSVN(pv.pkg_id) > 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,
               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 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,
               12 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,
               13 AS priority,
			   'ERROR' AS products_msg
		  FROM UNIT_TESTS ut
		 WHERE ut.PV_ID = :PV_ID

		) errmsg
ORDER BY priority