/* Added Components */
SELECT rc.NAME,
	   rc.BYTE_SIZE,
	   rc.CRC_CKSUM,
	   3 AS DIFF_STATE
  FROM (
  		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
			   rc.BYTE_SIZE, 
			   rc.CRC_CKSUM
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :PV_ID
		   AND rc.OS_ID = :OS_ID
  	   ) rc,
  	   (
		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :PV_ID
		   AND rc.OS_ID = :OS_ID
		MINUS 
		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :COMPARE_PV_ID 
		   AND rc.OS_ID = :OS_ID
	   ) crc
 WHERE rc.NAME = crc.NAME   
 
UNION 
 
/* Removed Components */
SELECT rc.NAME,
	   rc.BYTE_SIZE,
	   rc.CRC_CKSUM,
	   2 AS DIFF_STATE
  FROM (
  		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
			   rc.BYTE_SIZE, 
			   rc.CRC_CKSUM
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :COMPARE_PV_ID 
		   AND rc.OS_ID = :OS_ID
  	   ) rc,
  	   (
		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :COMPARE_PV_ID 
		MINUS 
		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :PV_ID 
		   AND rc.OS_ID = :OS_ID
	   ) crc
 WHERE rc.NAME = crc.NAME	   
 
UNION 
 
SELECT pv.NAME,
       pv.BYTE_SIZE, 
	   pv.CRC_CKSUM,
	   DECODE ( pv.CRC_CKSUM, cpv.CRC_CKSUM, 0, 1 ) AS DIFF_STATE
  FROM (
  		SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
			   rc.BYTE_SIZE, 
			   rc.CRC_CKSUM
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :PV_ID 
		   AND rc.OS_ID = :OS_ID
  	   ) pv,
	   (
	   	SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
   			   rc.BYTE_SIZE, 
			   rc.CRC_CKSUM
		  FROM PRODUCT_COMPONENTS rc
		 WHERE rc.PV_ID = :COMPARE_PV_ID
		   AND rc.OS_ID = :OS_ID
	   ) cpv
 WHERE pv.NAME = cpv.NAME	   
	   
		 
 
 
 
 