/* Recent BOMs in Branch */
SELECT bo.BOM_ID,
	   bn.BOM_NAME,
	   bo.BOM_VERSION,
	   bo.BOM_LIFECYCLE,
	   bo.IS_READONLY,
	   bo.IS_REJECTED
  FROM (
   		/* Head BOMs  */
		
		SELECT bo.BOM_ID
		  FROM BOMS bo
		 WHERE bo.BRANCH_ID = :BRANCH_ID
		 
		MINUS 
		
		SELECT bo.PARENT_BOM_ID AS BOM_ID
		  FROM BOMS bo
		 WHERE bo.BRANCH_ID = :BRANCH_ID
		   AND bo.BOM_ID != bo.PARENT_BOM_ID
		   
   	   ) hb,
	   BOM_NAMES bn,
	   BOMS bo
 WHERE bo.BOM_ID = hb.BOM_ID
   AND bo.BOM_NAME_ID = bn.BOM_NAME_ID		
 ORDER BY UPPER(bn.BOM_NAME), UPPER(bo.BOM_VERSION ||' '|| bo.BOM_LIFECYCLE ) DESC