This query will fetch the bill of material BOM detail with certain columns like assembly_type, org_code, PARENT_ITEM, comp_item, item_num, quantity, supply_subinventory in Oracle Apps R12
SELECT A.ASSEMBLY_TYPE, C.ORGANIZATION_CODE, (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = A.ASSEMBLY_ITEM_ID AND ORGANIZATION_ID = C.ORGANIZATION_ID) PARENT_ITEM, A.ALTERNATE_BOM_DESIGNATOR, B.ITEM_NUM, B.OPERATION_SEQ_NUM, (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = B.COMPONENT_ITEM_ID AND ORGANIZATION_ID = C.ORGANIZATION_ID) COMP_ITEM, B.COMPONENT_QUANTITY, B.WIP_SUPPLY_TYPE, B.SUPPLY_SUBINVENTORY, D.CONCATENATED_SEGMENTS --B.SUPPLY_LOCATOR_ID, --A.ASSEMBLY_ITEM_ID, --B.COMPONENT_ITEM_ID, --A.BILL_SEQUENCE_ID FROM ORG_ORGANIZATION_DEFINITIONS C, MTL_ITEM_LOCATIONS_KFV D, BOM_INVENTORY_COMPONENTS_V B, BOM_BILL_OF_MATERIALS A WHERE A.ORGANIZATION_ID = C.ORGANIZATION_ID AND B.SUPPLY_LOCATOR_ID = D.INVENTORY_LOCATION_ID(+) -- AND A.ORGANIZATION_ID = D.ORGANIZATION_ID(+) AND A.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID AND c.ORGANIZATION_CODE = :P_ORGANIZATION_CODE --AND A.ASSEMBLY_ITEM_ID = 1123 ORDER BY A.ASSEMBLY_ITEM_ID,B.ITEM_NUM;