SELECT msi.segment1 装配件,
       --msi.description,
       boms.alternate_bom_designator 替代,
       bomic.item_num                组件序号,
       bomic.operation_seq_num       工序号,
       msic.segment1                 组件编码,
       --msic.description,
       bomic.component_quantity     组件数量,
       bomic.effectivity_date       生效日期,
       bomic.planning_factor        计划百分比,
       bomic.component_yield_factor 产出率,
       lov.meaning                  供应类型,
       bomic.supply_subinventory    供应子库存
  FROM apps.bom_bill_of_materials    boms,
       inv.mtl_system_items_b        msi,
       apps.bom_inventory_components bomic,
       inv.mtl_system_items_b        msic,
       applsys.fnd_lookup_values     lov
WHERE msi.organization_id = 82
   AND boms.organization_id = msi.organization_id
   AND boms.assembly_item_id = msi.inventory_item_id
   AND boms.bill_sequence_id = bomic.bill_sequence_id
   AND nvl(bomic.disable_date, SYSDATE) >= SYSDATE
   AND nvl(bomic.effectivity_date, SYSDATE) <= SYSDATE
   AND bomic.component_item_id = msic.inventory_item_id
   AND msic.organization_id = boms.organization_id
   AND lov.lookup_type(+) = 'WIP_SUPPLY'
   AND lov.lookup_code(+) = bomic.wip_supply_type
   AND lov.LANGUAGE(+) = 'ZHS'
   --AND msi.segment1 = '4A001'
   --AND boms.alternate_bom_designator IS NULL
ORDER BY msi.segment1, bomic.item_num