SQL Scripts to print BOM Hierarchy

查询摩托车BOM结构

最新推荐文章于 2024-07-07 10:34:27 发布

最新推荐文章于 2024-07-07 10:34:27 发布 · 73 阅读

本文提供两种SQL查询方法，一种用于打印给定装配项的BOM层级结构，另一种则包括组件的数量信息。示例中使用了VisionDemo库中的AssemblyItem 75100021（摩托车组装成品物料）进行查询。

以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:

LEVEL	BILL_ITEM_NAME	ASSEMBLY_ITEM_ID	COMPONENT_ITEM_NAME	COMPONENT_ITEM_ID
1	75100021	6088	75100022	6090
2	75100022	6090	75100023	6092
3	75100023	6092	75100034	6126
1	75100021	6088	75100025	6094
2	75100025	6094	75100026	6128
1	75100021	6088	75100027	6130

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:

MATERIAL	PARENT_ITEM	ASSEMBLY_ITEM_ID	CHILD_ITEM	CHILD_ITEM_ID	BILL_SEQUENCE_ID	OPERATION_SEQ_NUM	LEVEL	COMPONENT_QUANTITY
Material	75100021	6088	75100022	6090	23632	10	1	1
Material	75100022	6090	75100023	6092	23660	10	2	1
Material	75100023	6092	75100034	6126	23663	10	3	0.3
Material	75100021	6088	75100025	6094	23632	10	1	1
Material	75100025	6094	75100026	6128	23666	10	2	1
Material	75100021	6088	75100027	6130	23632	10	1	1