Oracle EBS-SQL (BOM-15):检查多层BOM(含common BOM).sql
发布日期:2025-04-29 11:43:17 浏览次数:18 分类:精选文章

本文共 1993 字,大约阅读时间需要 6 分钟。

????????????????????????

???????????????????????????????????????????????????????????????????????????????

????

??????????????????

  • inv.mtl_system_items_b ? msi1 ? msi2
  • apps.BOM_BILL_OF_MATERIALS ? bombom
  • apps.BOM_INVENTORY_COMPONENTS ? bic

?????????

  • ??????????????
  • ????????????
  • ??????????????
  • ??????

    ????????????

    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,    b.COMPONENT_SEQUENCE_ID,    b.item_num,    b.wip_supply_type,    b.supply_subinventory,    b.effectivity_dateFROM     inv.mtl_system_items_b msi1,    inv.mtl_system_items_b msi2,    apps.BOM_BILL_OF_MATERIALS bom,    apps.BOM_INVENTORY_COMPONENTS bicWHERE     bom.ORGANIZATION_ID IN (1, 157)    AND bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID    AND msi1.ORGANIZATION_ID = bom.ORGANIZATION_ID    AND msi2.ORGANIZATION_ID = bom.ORGANIZATION_ID    AND b.component_item_id = msi2.INVENTORY_ITEM_ID    AND msi2.inventory_item_status_code > 'Inactive'    AND disable_date IS NULL    AND bic.IMPLEMENTATION_DATE IS NOT NULL    AND bom.alternate_bom_designator IS NULLCONNECT BY     bill_sequence_id IN (        SELECT             distinct 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    )ORDER BY     b.lvl

    ??????

  • ???????distinct??????????????
  • ????????CONNECT BY??????????????????
  • WHERE????????????????????
  • ???????????NULL?IS NOT NULL??????????
  • ????

    ???????????????????????????????????????????????????????????30%?

    ??

    ???????????????????????????????????????????????????????????????????????????????

    上一篇:Oracle EBS环境下查找数据源(OAF篇)
    下一篇:Oracle EBS OPM 发放生产批

    发表评论

    最新留言

    很好
    [***.229.124.182]2026年06月14日 23时43分20秒