/* Release Diff */
SELECT qry.*
  FROM (
  
		/* Removed Patches*/
		SELECT 'R' AS CHANGE_TYPE,
			   oldbom.PKG_ID,
			   oldbom.PV_ID AS PV_ID_A,
		       oldbom.PKG_NAME AS PKG_NAME_A,
			   oldbom.PKG_VERSION AS PKG_VERSION_A,
			   oldbom.NODE_NAME AS NODE_NAME_A,
			   oldbom.OS_NAME AS OS_NAME_A,
			   oldbom.PRODUCT_COMMENTS AS PRODUCT_COMMENTS_A,
			   NULL AS PV_ID_B,
			   NULL AS PKG_NAME_B,
			   NULL AS PKG_VERSION_B,
			   NULL AS NODE_NAME_B,
			   NULL AS OS_NAME_B,
			   NULL AS OS_ID_B,
			   NULL AS PRODUCT_COMMENTS_B
		  FROM (
		  		SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_B,
					   osc.PRODUCT_COMMENTS	
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   PACKAGES pkg
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_B
				   AND pkg.PKG_ID = pv.PKG_ID				   	
		  	   ) newbom,
			   (
			   	SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME, os.OS_NAME,
					   osc.PRODUCT_COMMENTS					   
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   PACKAGES pkg
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_A	
				   AND pkg.PKG_ID = pv.PKG_ID

			   ) oldbom
		 WHERE newbom.PK (+) = oldbom.PK  
		   AND newbom.PROD_ID (+) = oldbom.PROD_ID  
		   AND newbom.PK IS NULL  
	   
		   		
		
		UNION

		/* Removed Products*/
		SELECT 'R' AS CHANGE_TYPE,
			   oldbom.PKG_ID,
			   oldbom.PV_ID AS PV_ID_A,
		       oldbom.PKG_NAME AS PKG_NAME_A,
			   oldbom.PKG_VERSION AS PKG_VERSION_A,
			   oldbom.NODE_NAME_A,
			   oldbom.OS_NAME_A,
			   oldbom.PRODUCT_COMMENTS AS PRODUCT_COMMENTS_A,
			   NULL AS PV_ID_B,
			   NULL AS PKG_NAME_B,
			   NULL AS PKG_VERSION_B,
			   NULL AS NODE_NAME_B,
			   NULL AS OS_NAME_B,
			   NULL AS OS_ID_B,
			   NULL AS PRODUCT_COMMENTS_B
			 FROM (			   
		  		SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_ID, nn.NODE_NAME AS NODE_NAME_B,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT, osc.PRODUCT_COMMENTS
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   PACKAGES pkg
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_B	
				   AND pkg.PKG_ID = pv.PKG_ID
		  	   ) newbom,
			   (
			   	SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_ID, nn.NODE_NAME AS NODE_NAME_A, os.OS_NAME AS OS_NAME_A,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT, osc.PRODUCT_COMMENTS
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   PACKAGES pkg
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_A
				   AND pkg.PKG_ID = pv.PKG_ID
			   ) oldbom
		 WHERE newbom.PK (+) = oldbom.PK  
		   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
		   AND newbom.PKG_ID (+) = oldbom.PKG_ID
		   AND newbom.V_EXT (+) = oldbom.V_EXT   
		   AND newbom.PK IS NULL
		   		
		
		UNION
		   
		   
		/* Added Patches */   
		SELECT 'A' AS CHANGE_TYPE,
			   newbom.PKG_ID,
			   NULL AS PV_ID_A,
			   NULL AS PKG_NAME_A,
			   NULL AS PKG_VERSION_A,
			   NULL AS NODE_NAME_A,
			   NULL AS OS_NAME_A,
			   NULL AS PRODUCT_COMMENTS_A,
			   newbom.PV_ID AS PV_ID_B,
			   newbom.PKG_NAME AS PKG_NAME_B,
			   newbom.PKG_VERSION AS PKG_VERSION_B,
			   newbom.NODE_NAME_B,
			   newbom.OS_NAME_B,
			   newbom.OS_ID_B,
			   newbom.PRODUCT_COMMENTS AS PRODUCT_COMMENTS_B
		  FROM (
		  		SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_B, os.OS_NAME AS OS_NAME_B,
					   osc.PRODUCT_COMMENTS, osc.OS_ID AS OS_ID_B
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   PACKAGES pkg
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_B
				   AND pkg.PKG_ID = pv.PKG_ID
		  	   ) newbom,
			   (
			   	SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   osc.PRODUCT_COMMENTS
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_A
			   ) oldbom
		 WHERE newbom.PK = oldbom.PK (+) 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
		   AND oldbom.PK IS NULL	   
		   
		UNION

		/* Added Products */   
		SELECT 'A' AS CHANGE_TYPE,
			   newbom.PKG_ID,
			   NULL AS PV_ID_A,
			   NULL AS PKG_NAME_A,
			   NULL AS PKG_VERSION_A,
			   NULL AS NODE_NAME_A,
			   NULL AS OS_NAME_A,
			   NULL AS PRODUCT_COMMENTS_A,
			   newbom.PV_ID AS PV_ID_B,
			   newbom.PKG_NAME AS PKG_NAME_B,
			   newbom.PKG_VERSION AS PKG_VERSION_B,
			   newbom.NODE_NAME_B,
			   newbom.OS_NAME_B,
			   newbom.OS_ID_B,
			   newbom.PRODUCT_COMMENTS AS PRODUCT_COMMENTS_B
		  FROM (
		  		SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_ID, nn.NODE_NAME AS NODE_NAME_B, os.OS_NAME AS OS_NAME_B,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT, osc.PRODUCT_COMMENTS,
					   osc.OS_ID AS OS_ID_B
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   PACKAGES pkg
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_B
				   AND pkg.PKG_ID = pv.PKG_ID	
		  	   ) newbom,
			   (
			   	SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT, 
					   osc.PRODUCT_COMMENTS
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE osc.OS_ID = os.OS_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND osc.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :SBOM_A
			   ) oldbom
		 WHERE newbom.PK = oldbom.PK (+) 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
		   AND newbom.V_EXT  = oldbom.V_EXT (+) 
		   AND oldbom.PROD_ID IS NULL   	    
		   
		UNION
		   
		/* UPDATED Packages */   
		SELECT ( 
				CASE WHEN ra.PV_ID < rb.PV_ID THEN
					'UW'
				ELSE
					'U'
				END
				) AS CHANGE_TYPE,
			   ra.PKG_ID,
			   ra.PV_ID AS PV_ID_A,
		       ra.PKG_NAME AS PKG_NAME_A,
			   ra.PKG_VERSION AS PKG_VERSION_A,
			   ra.NODE_NAME_A,
			   ra.OS_NAME_A,
			   ra.PRODUCT_COMMENTS AS PRODUCT_COMMENTS_A,
			   rb.PV_ID AS PV_ID_B,
			   rb.PKG_NAME AS PKG_NAME_B,
			   rb.PKG_VERSION AS PKG_VERSION_B,
			   rb.NODE_NAME_B,
			   rb.OS_NAME_B,
			   rb.OS_ID_B,
			   rb.PRODUCT_COMMENTS AS PRODUCT_COMMENTS_B
		  FROM (
		   	   SELECT osc.PROD_ID, pv.PV_ID, pv.V_EXT, pv.PKG_VERSION, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_A,
			   os.OS_NAME AS OS_NAME_A, osc.PRODUCT_COMMENTS
			   FROM OS_CONTENTS osc,
			  	    OPERATING_SYSTEMS os,
					NETWORK_NODES nn,
					BOM_CONTENTS bc,
					PACKAGE_VERSIONS pv,
					PACKAGES pkg
			  WHERE osc.OS_ID = os.OS_ID
				AND os.NODE_ID = nn.NODE_ID
				AND bc.NODE_ID = nn.NODE_ID
				AND osc.PROD_ID = pv.PV_ID
				AND pv.IS_PATCH IS NULL
				AND bc.BOM_ID = :SBOM_A
				AND pkg.PKG_ID = pv.PKG_ID
		       ) ra,
			   (
			   SELECT osc.PROD_ID, pv.PV_ID, pv.V_EXT, pv.PKG_VERSION, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_B,
			   os.OS_NAME AS OS_NAME_B, osc.PRODUCT_COMMENTS, osc.OS_ID AS OS_ID_B
			   FROM OS_CONTENTS osc,
			  	    OPERATING_SYSTEMS os,
					NETWORK_NODES nn,
					BOM_CONTENTS bc,
					PACKAGE_VERSIONS pv,
					PACKAGES pkg
			  WHERE osc.OS_ID = os.OS_ID
				AND os.NODE_ID = nn.NODE_ID
				AND bc.NODE_ID = nn.NODE_ID
				AND osc.PROD_ID = pv.PV_ID
				AND pv.IS_PATCH IS NULL
				AND bc.BOM_ID = :SBOM_B	
				AND pv.PKG_ID = pkg.PKG_ID
				AND osc.PROD_ID NOT IN (
				SELECT osc.PROD_ID
			   FROM OS_CONTENTS osc,
			  	    OPERATING_SYSTEMS os,
					NETWORK_NODES nn,
					BOM_CONTENTS bc,
					PACKAGE_VERSIONS pv
			  WHERE osc.OS_ID = os.OS_ID
				AND os.NODE_ID = nn.NODE_ID
				AND bc.NODE_ID = nn.NODE_ID
				AND osc.PROD_ID = pv.PV_ID
				AND pv.IS_PATCH IS NULL
				AND bc.BOM_ID = :SBOM_A
				)
			   ) rb
		 WHERE ra.PKG_ID = rb.PKG_ID
		   AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')  
		   AND ra.PV_ID != rb.PV_ID  
		   AND ra.NODE_NAME_A = rb.NODE_NAME_B
		   AND ra.OS_NAME_A = rb.OS_NAME_B
  		) qry
		WHERE PV_ID_B = :PV_ID
 ORDER BY UPPER(qry.PKG_NAME_A) 
