/* Issue List */

SELECT cq.iss_id,
	   cq.iss_db,
	   cq.pv_id,
	   pkg.pkg_name,
	   pv.pkg_version,
	   DECODE( cq.iss_state,
	   		   1, DECODE ( isspv.status,
			   	  		   1, 'LIMG_FIXED',
						   2, 'LIMG_OLD_FIXED' ),
			   '' ) AS ICON_FIXED
  FROM 
  	   (
		   (
		    /* Modules updated + added in this new Product */
			
			/* Original Product */
			SELECT 1 AS STATUS, dep.dpv_id
			  FROM PACKAGE_DEPENDENCIES dep
			 WHERE dep.PV_ID = :PROD_ID
			MINUS
			/* Previous Version */ 
			SELECT 1 AS STATUS, dep.dpv_id
			  FROM PACKAGE_DEPENDENCIES dep
			 WHERE dep.PV_ID = :OLD_PROD_ID  
               AND :PROD_ID != :OLD_PROD_ID 
			UNION
		   	/* Product itself */
			SELECT 1 AS STATUS, TO_NUMBER( :PROD_ID ) FROM DUAL
			
		   )	
		   
		   UNION
		   
		   (
		   	/* Modules + Products itself of other versions */
			
		    SELECT 2 AS STATUS, dep.DPV_ID
			  FROM PACKAGE_DEPENDENCIES dep
			 WHERE dep.pv_id IN ( :OTHER_PROD_ID )
			 UNION
			SELECT 2 AS STATUS, TO_NUMBER( :OTHER_PROD_ID ) FROM DUAL
			
			MINUS
			
			(
		    /* Modules updated + added in this new Product */
			
			/* Original Product */
			SELECT 2 AS STATUS, dep.dpv_id
			  FROM PACKAGE_DEPENDENCIES dep
			 WHERE dep.PV_ID = :PROD_ID
			MINUS
			/* Previous Version */ 
			SELECT 2 AS STATUS, dep.dpv_id
			  FROM PACKAGE_DEPENDENCIES dep
			 WHERE dep.PV_ID = :OLD_PROD_ID   
               AND :PROD_ID != :OLD_PROD_ID   
			UNION
		   	/* Product itself */
			SELECT 2 AS STATUS, TO_NUMBER( :PROD_ID ) FROM DUAL
			
		   )	
			 
		   ) 
	   ) isspv,
	   
	   CQ_ISSUES cq,
	   PACKAGE_VERSIONS pv,
	   PACKAGES pkg
	   
	   
 WHERE isspv.dpv_id = cq.pv_id
   AND pv.pkg_id = pkg.pkg_id
   AND pv.pv_id = isspv.dpv_id
   AND cq.iss_db = 7 
 