/* Bom Compare */
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,
		 pkg.PKG_ID,
		 qry.node_id,
		 qry.node_name,
		 qry.node_icon,
		 qry.os_id,
		 qry.os_name,
		 qry.is_obsolete			 
  FROM (
  
  		/* ADDED PATCHES ONLY */
		SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK, 
		newbom.node_id, newbom.node_name, newbom.node_icon, newbom.os_id,
		newbom.os_name, newbom.is_obsolete 
		  FROM (
		  		SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
					   nn.NODE_ID,
					   nn.NODE_NAME,
					   nt.NODE_ICON,
				                 os.OS_ID,
					   os.OS_NAME,
					   pv.IS_OBSOLETE		
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   NODE_TYPES nt
				 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 pv.IS_OBSOLETE IS NULL
				   AND bc.BOM_ID = :BOM_ID
				   and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID	
		  	   ) newbom,
			   (
			   	SELECT osc.PROD_ID,
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
				  FROM OS_CONTENTS osc,
				  	   OPERATING_SYSTEMS os,
					   NETWORK_NODES nn,
					   BOM_CONTENTS bc,
					   PACKAGE_VERSIONS pv,
					   NODE_TYPES nt
				 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 = :COMPARE_BOM_ID	
				   and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
			   ) oldbom
		 WHERE newbom.PK = oldbom.PK (+) 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
		   AND oldbom.PK IS NULL
		   AND newbom.PROD_ID NOT IN 
		   (
		   
		   SELECT pv.PV_ID AS PROD_ID
           FROM bom_contents bc,
                operating_systems os,
				network_nodes nn,
				node_types nt,
                os_contents osc,
                PACKAGES pkg,
                package_versions pv,
				product_details pd
          WHERE bc.bom_id = :current_bom_id
            AND os.node_id = bc.node_id
            AND osc.os_id = os.os_id
            AND osc.prod_id = pv.pv_id
            AND pkg.pkg_id = pv.pkg_id
			and pd.PROD_ID(+) = osc.PROD_ID
			and nn.node_id = os.node_id
			and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
			and pkg.pkg_id = :PKG_ID
		   )
		   
	
		   
       ) 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
   AND pkg.PKG_ID = :PKG_ID
   
   
 
  
  
