oracle展bom逻辑,Oracle MASTER BOM展开-- 一句SQL搞定，以后展开BOM不用愁啦

这是俺原创的，速度很快，写个循环就可以导出多个编码的BOM树，使用时把SQL中的“组织ID”换成自己EBS的实际ID。

初次写这种语句，好用支持一下！

SELECT distinct

bb.BOM层次,

bb.装配件,

bb.装配件说明,

bb.组件序号,

bb.组件,

bb.组件说明,

bb.组件用量,

bb.单位,

bb.利用率,

bb.冲减库房,

bb.冲减货位,

bb.属性,

bb.BOM创建时间,

BOR.COMPLETION_SUBINVENTORY 入库,

MSI.WIP_SUPPLY_SUBINVENTORY 冲减,

MSI.MAXIMUM_ORDER_QUANTITY  最大批量,

MSI.LEAD_TIME_LOT_SIZE      提前期,

MSI.FULL_LEAD_TIME          制造周期,

MSI.FIXED_LOT_MULTIPLIER    固定增加,

MSI.MINIMUM_ORDER_QUANTITY  最小批量,

MSI.FIXED_ORDER_QUANTITY    固定定货量,

MSI.FIXED_DAYS_SUPPLY       固定天数,

MSI.PLANNER_CODE            计划员

FROM MTL_SYSTEM_ITEMS          MSI,

BOM_OPERATIONAL_ROUTINGS  BOR,

BOM_OPERATION_SEQUENCES   BOS,

(select distinct

aa.lvl                       BOM层次,

msi.segment1                 装配件,

msi.description              装配件说明,

aa.item_num                  组件序号,

msi1.segment1                组件,

msi1.description             组件说明,

aa.component_quantity        组件用量,

msi1.primary_unit_of_measure 单位,

aa.COMPONENT_YIELD_FACTOR    利用率,

msi1.wip_supply_subinventory 冲减库房,

mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||

mil.segment4                 冲减货位,

decode(msi1.planning_make_buy_code,1,'制造',2,'采购') 属性,

to_char(msi1.creation_date,'yyyy.mm.dd hh24:mm.ss')   BOM创建时间,

msi1.inventory_item_id

from mtl_system_items_b       msi,

mtl_system_items_b       msi1,

bom_bill_of_materials    bom,

bom_inventory_components bic,

mtl_item_locations       mil,

(

select level lvl,

bic.bill_sequence_id,

bic.component_item_id,

bic.component_quantity,

bic.OPERATION_SEQ_NUM,

bic.COMPONENT_YIELD_FACTOR,

bic.COMPONENT_SEQUENCE_ID,

bic.item_num,

bic.wip_supply_type,

bic.supply_subinventory,

bic.effectivity_date

FROM bom_inventory_components bic

where disable_date IS NULL

start with bic.bill_sequence_id in

(select bill_sequence_id

from bom_bill_of_materials bom2,

inv.mtl_system_items_b    msi

where bom2.assembly_item_id = msi.inventory_item_id

and bom2.organization_id = msi.organization_id

---------------------------------------输入要查询的项目---------------------------------------------

and msi.segment1 ='料号名称'

---------------------------------------输入要查询的项目---------------------------------------------

and msi.organization_id = 组织ID

and bom2.alternate_bom_designator is null)

CONNECT BY bic.bill_sequence_id in prior

(SELECT distinct bill_sequence_id

FROM bom_bill_of_materials BO, inv.mtl_system_items_b msi

WHERE BO.assembly_item_id = bic.component_item_id

AND BO.organization_id = 组织ID

and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID

and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID

and bo.alternate_bom_designator is null

and disable_date IS NULL)) aa

where msi.organization_id=组织ID

and msi1.organization_id=组织ID

and bom.organization_id=组织ID

and msi.inventory_item_id=bom.assembly_item_id

and bom.bill_sequence_id=bic.bill_sequence_id

and bic.component_item_id=msi1.inventory_item_id

and bic.disable_date is null

and aa.bill_sequence_id=bic.bill_sequence_id

and aa.component_item_id=msi1.inventory_item_id

and mil.inventory_location_id(+)=msi1.wip_supply_locator_id

and mil.organization_id(+)=组织ID

order by

aa.lvl,

aa.item_num) bb

WHERE BOR.ASSEMBLY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID

AND BOS.ROUTING_SEQUENCE_ID(+) = BOR.ROUTING_SEQUENCE_ID

AND MSI.ORGANIZATION_ID(+) = 组织ID

and bor.alternate_routing_designator is null

and bb.inventory_item_id=msi.inventory_item_id

order by

bb.BOM层次,

bb.装配件,

bb.装配件说明,

bb.组件序号,

bb.组件