shuaiwei588 发表于 2015-9-23 12:42:51

Oracle EBS-SQL (BOM-15):检查多层BOM(含common BOM).sql

  select       distinct
  b.lvl                                                      层次,
  b.OPERATION_SEQ_NUM                        工序,
  msi1.segment1                                 父件编码,
  msi1.description                              父件描述,
  msi1.item_type                                 父件类型,
  msi1.inventory_item_status_code 父件编码状态,
  msi2.segment1                                 子件编码,
  msi2.description                              子件描述,
  msi2.item_type                                 子件类型,
  b.component_quantity                           用量,
  b.COMPONENT_YIELD_FACTOR            产出率
  from inv.mtl_system_items_b msi1,
  inv.mtl_system_items_b msi2,
  apps.BOM_BILL_OF_MATERIALS bom,
  (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 apps.BOM_INVENTORY_COMPONENTS bic
  where bic.disable_date IS NULL
  and bic.IMPLEMENTATION_DATE is not null
  start with bic.bill_sequence_id in
  (select nvl(common_bill_sequence_id,bill_sequence_id)
  from apps.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.organization_id in(1,157)
  and bom2.alternate_bom_designator is null)
  CONNECT BY bill_sequence_id in prior
  (SELECT distinct nvl(common_bill_sequence_id,bill_sequence_id)
  FROM apps.BOM_BILL_OF_MATERIALS BO,
  inv.mtl_system_items_b    msi
  WHERE BO.assembly_item_id = bic.component_item_id
  AND BO.organization_id in(1,157)
  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)) b
  where b.bill_sequence_id = nvl(bom.common_bill_sequence_id,bom.bill_sequence_id)
  and bom.ORGANIZATION_ID in(1,157)
  and bom.ORGANIZATION_ID = msi1.ORGANIZATION_ID
  and bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID
  and bom.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
  AND b.component_item_id = MSI2.INVENTORY_ITEM_ID
  and msi2.inventory_item_status_code <> 'Inactive'
  order by b.lvl
  
  
  
页: [1]
查看完整版本: Oracle EBS-SQL (BOM-15):检查多层BOM(含common BOM).sql