SELECT qry.*
  FROM (
  
		/* All states for this project */
		SELECT st.state_id,
			   st.state_name,
			   st.STATE_SEQ,
			   st.STATE_TYPE_ENUM,
			   TO_NUMBER(NULL) AS bom_id,
			   NULL AS BOM_NAME,
			   NULL AS BOM_VERSION,
			   NULL AS BOM_LIFECYCLE,
			   TO_DATE(NULL) AS DATE_STAMP,
			   NULL AS IS_READONLY,
			   NULL AS IS_REJECTED
		  FROM STATES st
		 WHERE st.PROJ_ID = :PROJ_ID  
		 
		UNION 
		 
		/* All boms for this branch */ 
		SELECT st.state_id,
			   st.state_name,
			   st.STATE_SEQ,
			   st.STATE_TYPE_ENUM,
			   bm.bom_id,
			   bn.BOM_NAME,
			   bm.BOM_VERSION,
			   bm.BOM_LIFECYCLE,
			   bs.DATE_STAMP,
			   bm.IS_READONLY,
			   bm.IS_REJECTED
		  FROM BOM_STATE bs,
		  	   BOM_NAMES bn,
		  	   BOMS bm,
			   STATES st
		 WHERE bs.BOM_ID = bm.bom_id
		   AND bs.STATE_ID = st.state_id
		   AND bm.bom_name_id = bn.bom_name_id
		   AND bm.branch_id IN (
		   	   				  	  /* Ensure branch is from this project */
		   	   				  	  SELECT br.branch_id 
								    FROM DM_PROJECTS proj,
										 BRANCHES br
								   WHERE br.PROJ_ID = proj.proj_id
								     AND br.branch_id = :BRANCH_ID
									 AND proj.proj_id = :PROJ_ID
		   	   				  	 ) 
		   AND bs.IS_DISPLAYED = 'Y'
		   
		  ) qry
 ORDER BY   	qry.STATE_SEQ,
		UPPER(qry.BOM_NAME) NULLS FIRST,
		qry.DATE_STAMP DESC,
		(qry.BOM_VERSION) DESC,
		 TO_NUMBER(qry.BOM_LIFECYCLE) DESC
    	 	
 	   	  
