/* Bom Tree Combo For SAN FRANCISCO*/
SELECT DISTINCT bo.BOM_ID,
	   bo.BOM_VERSION ||'.'|| bo.BOM_LIFECYCLE AS BOM_VERSION,
	   DECODE( bo.BOM_ID,
	   		   :BRANCH_ID, 'selected',
			   NULL ) AS SELECTED,
	   bn.BOM_NAME
  FROM ( 
		SELECT bo.BOM_ID, bo.BOM_VERSION ||'.'|| bo.BOM_LIFECYCLE AS VERSION
		  FROM BOMS bo, BOM_STATE bs, PRODUCTION_BOM pm
		 WHERE bo.BRANCH_ID = :BRANCH_ID
		 AND bs.STATE_ID = pm.STATE_ID
		 AND bs.BOM_ID = bo.BOM_ID
	  ) qry,
	  BOMS bo, BOM_NAMES bn, BOM_STATE bs
 WHERE qry.BOM_ID = bo.BOM_ID	
 AND bn.BOM_NAME_ID = bo.BOM_NAME_ID
 AND bn.BOM_NAME = :BOM_NAME
 AND bs.BOM_ID = bo.BOM_ID
 AND bs.IS_DISPLAYED = 'Y'
AND (bo.is_rejected IS NULL OR bo.IS_REJECTED = 'N')
ORDER BY bo.BOM_ID desc
