Oracle MASTER BOM展开-- 一句SQL搞定，以后展开BOM不用愁啦

wlsing2008 · 发表于 2008-1-15 16:45

學習

wlsing2008 · 发表于 2008-1-15 16:46

這個和bompexpl.EXPLODER_USEREXIT展出來的有什么不一樣的嗎？請教

terencelin1226 · 发表于 2008-1-17 10:15

LZ很厲害哦!!!
雖然速度有點慢，
不過的確是展出來了，
只是少了替代料，
不過還是讚!!!

wenvay · 发表于 2008-1-21 14:31

原帖由 ysfabm 于 2008-1-14 17:04 发表
这是俺原创的，速度很快，写个循环就可以导出多个编码的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.组件