
SELECT qry.*
  FROM (
  
  	   	/* NETWORK NODE LEVEL */
		SELECT DECODE ( COUNT( os.os_id ),
			   		  	0, NULL,
						NULL, NULL,
						DECODE ( parent.node_id,
							     :node_id, '-',
								 '+' 
								)
					  ) AS expand,
			   parent.NODE_SEQ_NUM,
			   parent.node_id,
			   parent.NODE_NAME,
			   parent.NODE_ICON,
			   parent.os_id,
			   parent.OS_NAME,
			   parent.BOS_ICON,
			   parent.prod_id,
			   parent.prod_name,
			   parent.SEQ_NUM,
			   parent.CHANGE_LOG_FLAG,
			   NULL AS IS_REJECTED,
			   parent.NODE_VERSION,
			   parent.NODE_LIFE_CYCLE
		  FROM OPERATING_SYSTEMS os,
		  	   (	
			    SELECT nn.node_id AS node_id,
					   nn.NODE_NAME,
					   nt.NODE_ICON,
					   0 AS os_id,
					   '-' AS OS_NAME,
					   NULL AS BOS_ICON,
					   0 AS prod_id,
					   '-' AS prod_name,
					   0 AS SEQ_NUM,
					   NULL AS CHANGE_LOG_FLAG,
					   nn.NODE_VERSION,
			   		   nn.NODE_LIFE_CYCLE,
					   bc.SEQ_NUM AS NODE_SEQ_NUM
				  FROM NETWORK_NODES nn,
				  	   BOM_CONTENTS bc,
					   NODE_TYPES nt					   
				 WHERE bc.node_id = nn.node_id
				   AND nn.node_type_id = nt.node_type_id
				   AND bc.bom_id IN (
				   	   			  	  /* Ensures that node_id IS IN bom_id */
				   	   			  	  SELECT DISTINCT bc.bom_id
									    FROM BOM_CONTENTS bc
									   WHERE bc.bom_id = :bom_id
									     AND ( 
										   	    ( bc.node_id = bc.node_id AND :node_id = -1 ) OR
												( bc.node_id = :node_id 	      AND :node_id != -1 ) 
										   	 )  
				   	   			  	 )
			   ) parent	   			   
		 WHERE os.node_id (+) = parent.node_id
		 GROUP BY  parent.NODE_SEQ_NUM,
		 		   parent.node_id,
		 	   	   parent.NODE_NAME,
				   parent.NODE_ICON,
				   parent.os_id,
				   parent.OS_NAME,
				   parent.BOS_ICON,
				   parent.prod_id,
				   parent.prod_name,
				   parent.SEQ_NUM,
				   parent.CHANGE_LOG_FLAG,
				   parent.NODE_VERSION,
			   	   parent.NODE_LIFE_CYCLE
				   
		   
		   
		UNION
		
		
		/* OS LEVEL */
		SELECT DECODE ( COUNT( * ),
			   		  	0, NULL,
						NULL, NULL,
						DECODE ( parent.node_id, 
								 :node_id, DECODE ( parent.os_id, 
								 		   		    :os_id, '-',
													DECODE ( parent.node_id,
														   	 :node_id, DECODE ( :os_id, -1, '-', '+')
														   )
												  )
								)
					  ) AS expand,
			   parent.NODE_SEQ_NUM,
			   parent.node_id,
			   parent.NODE_NAME,
			   parent.NODE_ICON,
			   parent.os_id,
			   parent.OS_NAME,
			   parent.BOS_ICON,
			   parent.prod_id,
			   parent.prod_name,
			   parent.SEQ_NUM,
			   parent.CHANGE_LOG_FLAG,
			   NULL AS IS_REJECTED,
			   parent.NODE_VERSION,
			   parent.NODE_LIFE_CYCLE
		  FROM OS_CONTENTS osc,
		  	   (	
				SELECT nn.node_id AS node_id,
					   nn.NODE_NAME,
					   NULL AS NODE_ICON,
					   os.os_id AS os_id,
					   os.OS_NAME,
					   DECODE ( bt.BOS_ICON,
					   		  	NULL, 'i_generic_os.gif',
								bt.BOS_ICON
					   		  ) BOS_ICON,
					   0 AS prod_id,
					   '-' AS prod_name,
					   0 AS SEQ_NUM,
					   NULL AS CHANGE_LOG_FLAG,
					   NULL AS NODE_VERSION,
			   		   0 AS NODE_LIFE_CYCLE,
					   bc.SEQ_NUM AS NODE_SEQ_NUM
				  FROM NETWORK_NODES nn,
				  	   BOM_CONTENTS bc,
					   OPERATING_SYSTEMS os,
					   BASE_ENV be,
					   OS_BASE_ENV osbe,
					   BOS_TYPES bt
				 WHERE bc.node_id = nn.node_id
				   AND os.node_id = nn.node_id
				   AND osbe.OS_ID (+) = os.OS_ID
				   AND osbe.BASE_ENV_ID = be.base_env_id (+)
				   AND be.BOS_ID = bt.bos_id (+)
				   AND bc.bom_id = :bom_id  
				   AND nn.node_id = :node_id
			   ) parent	   			   
		 WHERE osc.os_id (+) = parent.os_id
		 GROUP BY  parent.NODE_SEQ_NUM,
		 		   parent.node_id,
		 	   	   parent.NODE_NAME,
				   parent.NODE_ICON,
				   parent.os_id,
				   parent.OS_NAME,
				   parent.BOS_ICON,
				   parent.prod_id,
				   parent.prod_name,
				   parent.SEQ_NUM,
				   parent.CHANGE_LOG_FLAG,
				   parent.NODE_VERSION,
			   	   parent.NODE_LIFE_CYCLE 

		  
		UNION   
		 
		 
		/* PRODUCTS LEVEL */   
		SELECT NULL AS expand,
			   bc.SEQ_NUM AS NODE_SEQ_NUM,
			   nn.node_id AS node_id,
			   nn.NODE_NAME,
			   NULL AS NODE_ICON,
			   os.os_id AS os_id,
			   os.OS_NAME,
			   NULL AS BOS_ICON,
			   pv.pv_id AS prod_id,
			   pkg.pkg_name || ' ' || pv.pkg_version AS prod_name,
			   osc.SEQ_NUM,
			   osc.CHANGE_LOG_FLAG,
			   pd.IS_REJECTED,
			   NULL AS NODE_VERSION,
			   0 AS NODE_LIFE_CYCLE
		  FROM NETWORK_NODES nn,
		  	   BOM_CONTENTS bc,
			   OPERATING_SYSTEMS os,
			   OS_CONTENTS osc,
			   PACKAGES pkg,
			   PACKAGE_VERSIONS pv,
			   PRODUCT_DETAILS pd
		 WHERE pd.PROD_ID (+) = pv.PV_ID
		   AND bc.node_id = nn.node_id
		   AND os.node_id = nn.node_id
		   AND pv.pkg_id = pkg.pkg_id
		   AND osc.PROD_ID = pv.pv_id
		   AND osc.os_id = os.os_id
		   AND bc.bom_id = :bom_id  
		   AND nn.node_id = :node_id			   
		   AND ( 
			  	   ( os.os_id = os.os_id	AND :os_id = -1 ) OR
			   	   ( os.os_id = :os_id	AND :os_id != -1 ) 
			   )
				   
		  ) qry
		    
 ORDER BY qry.NODE_SEQ_NUM, UPPER(qry.node_name), UPPER(qry.os_name), qry.seq_num
 