/* Bom Compare - Base Configuration */
SELECT DISTINCT  
       pv.PV_ID AS PROD_ID,
	   pkg.PKG_NAME,
	   pv.PKG_VERSION,
	   qry.CHANGE_TYPE,
	   pd.IS_REJECTED,
	   ( CASE 
	     WHEN qry.CHANGE_TYPE = 'A' THEN
	   		'LIMG_ADDED'
		 WHEN qry.CHANGE_TYPE = 'U' THEN
			'LIMG_UPDATED'
		 ELSE
		 	'LIMG_REMOVED'
		 END ) AS DIFF_ICON			 
  FROM (
  
  		/* ADDED PATCHES ONLY - BASE CONFIGURATIONS*/
		SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK 
		  FROM (
		  		SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND obe.BASE_ENV_ID = bec.BASE_ENV_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :BOM_ID	
		  	   ) newbom,
			   (
			   	SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID	   
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :COMPARE_BOM_ID	
			   ) oldbom
		 WHERE newbom.PK = oldbom.PK (+) 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
		   AND oldbom.PK IS NULL
		   
		UNION 
		   
		/* ADDED PRODUCTS ONLY - BASE CONFIGURATIONS */
		SELECT DECODE( oldbom.PROD_ID,
					   NULL, 'A', 'U' ) AS CHANGE_TYPE, 
			   newbom.PROD_ID, newbom.PK
		  FROM (
		  		SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID 	   
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :BOM_ID	
		  	   ) newbom,
			   (
			   	SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :COMPARE_BOM_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 ( oldbom.PROD_ID != newbom.PROD_ID  OR oldbom.PROD_ID IS NULL )
		   AND newbom.PROD_ID NOT IN 
		   (
		   	   SELECT bec.PROD_ID
			   FROM BASE_ENV_CONTENTS bec,
				  	OPERATING_SYSTEMS os,
					OS_BASE_ENV obe,
					NETWORK_NODES nn,
					BOM_CONTENTS bc,
					PACKAGE_VERSIONS pv
			  WHERE obe.OS_ID = os.OS_ID
			    AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
				AND os.NODE_ID = nn.NODE_ID
				AND bc.NODE_ID = nn.NODE_ID
				AND bec.PROD_ID = pv.PV_ID
				AND pv.IS_PATCH IS NULL
				AND bc.BOM_ID = :COMPARE_BOM_ID	
		  )	   

		UNION
		
		/* REMOVED PRODUCTS ONLY - BASE CONFIGURATIONS*/    
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
		  FROM (
		  		SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :BOM_ID	
		  	   ) newbom,
			   (
			   	SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   pv.PKG_ID,
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :COMPARE_BOM_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   
		   
		/* REMOVED PATCHES - BASE CONFIGURATIONS */    
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
		  FROM (
		  		SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :BOM_ID	
		  	   ) newbom,
			   (
			   	SELECT bec.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
				  FROM BASE_ENV_CONTENTS bec,
				  	   OPERATING_SYSTEMS os,
					   OS_BASE_ENV obe,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv
				 WHERE obe.OS_ID = os.OS_ID
				   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
				   AND os.NODE_ID = nn.NODE_ID
				   AND bc.NODE_ID = nn.NODE_ID
				   AND bec.PROD_ID = pv.PV_ID
				   AND NOT pv.IS_PATCH IS NULL
				   AND bc.BOM_ID = :COMPARE_BOM_ID	
			   ) oldbom
		 WHERE newbom.PK (+) = oldbom.PK  
		   AND newbom.PROD_ID (+) = oldbom.PROD_ID  
		   AND newbom.PK IS NULL  		   
		   
       ) qry,
	   PACKAGE_VERSIONS pv,
	   PACKAGES pkg,
	   PRODUCT_DETAILS pd  
 WHERE qry.PROD_ID = pv.PV_ID
   AND pv.PKG_ID = pkg.PKG_ID
   AND pd.PROD_ID (+) = qry.PROD_ID
 ORDER BY UPPER(pkg.PKG_NAME)   	