以Vision Demo库的Assembly Item:75100021为例(这个Item为摩托车组装成品物料)

Query1:Sql query to print BOM hierarchy for a given assembly item

select level,bill_item_name,assembly_item_id,component_item_name, component_item_id
from apps.bomfg_bom_components
start with bill_item_name = \'75100021\'   --Replace your assembly_item_id to here
connect by prior component_item_name = bill_item_name;

Output:

Query2:另外一个类似的脚本，通过Assembly查询BOM的组件情况(包含组件数量)

SELECT \'Material\' Material ,
       (SELECT msi.segment1
       FROM    mtl_system_items msi
       WHERE   msi.inventory_item_id=bom.assembly_item_id
       AND     msi.organization_id  =207  --Replace your organization_id to here
       )
       parent_item         ,
       bom.assembly_item_id,
       lpad(\'\',2*(level-1))
              ||
       (SELECT msi.segment1
       FROM    mtl_system_items msi
       WHERE   msi.inventory_item_id=bic.component_item_id
       AND     msi.organization_id  =207  --Replace your organization_id to here
       )
                             child_item   ,
       bic.component_item_id child_item_id,
       bic.bill_sequence_id               ,
       bic.operation_seq_num              ,
       level                              ,
       bic.component_quantity
FROM   bom_inventory_components bic,
       (SELECT *
       FROM    bom_bill_of_materials
       WHERE   organization_id=207 --Replace your organization_id to here
       )
       bom
WHERE  bom.bill_sequence_id=bic.bill_sequence_id 
START WITH bom.assembly_item_id= 6088 ----Replace your assembly_item_id to here 
CONNECT BY prior bic.component_item_id=bom.assembly_item_id;

Sample Output: