求BOM清單SQL語句

lveamy

肯定有的
select DISTINCT
msi1.segment1 P_item,
to_char(b.lvl) LV,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,
b.component_quantity Component_Qty
from inv.mtl_system_items msi1,
inv.mtl_system_items msi2,
bom.bom_bill_of_materials bom,
bom.bom_operation_sequences bos,
(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 bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
and msi.segment1='10022111742'
and msi.organization_id = 2
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id in
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 2
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 = bom.bill_sequence_id
and bom.ORGANIZATION_ID =2
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
order by 2
试试这个怎么样的...呵呵...

lveamy

个人感觉还是不错的....
其实这个你都可以做个report出来的了
那样看的话比较直观点

chtwo

那个SQL只取单层BOM，其实你的半成品还有自己的清单，在下面了，那段很简单，不是多级展开的，那样的话麻烦死了，偶也不会。

xing_lang

系統里有個功能可以PROCEDURE：bompexpl.explosion_report   可以研究哦！！

tommy_gu

最初由 xing_lang 发布
[B]系統里有個功能可以PROCEDURE：bompexpl.explosion_report   可以研究哦！！ [/B]

14樓說說看呢，具體是怎麼處理的呢?寫的太簡單.

brookdai

PROCEDURE bom_exp1(
                  ,p_item_id   NUMBER
                 ) IS

CURSOR CU_2(p_item_id NUMBER) IS

SELECT
       msi.segment1                SEG2   , --
       bic.component_sequence_id          ,
       bic.component_item_id              ,
       msi.INVENTORY_ITEM_ID
       ,bic.disable_date
FROM   bom_inventory_components bic,
       mtl_system_items_b        msi,
       bom_bill_of_materials    bbom,
WHERE      bbom.assembly_item_id   = p_item_id    --
       and bbom.organization_id    = &&1
       and bbom.alternate_bom_designator is null
       and (bbom.common_bill_sequence_id= bic.bill_sequence_id
       OR  bbom.bill_sequence_id= bic.bill_sequence_id)
       and bic.component_item_id = msi.inventory_item_id
       and nvl(bic.acd_type,'1')<>'3'
       and bic.implementation_date is not null
       and msi.organization_id  = &&1            
ORDER  BY msi.segment1;

LR_2   CU_2%ROWTYPE;

BEGIN

   FOR lr_2 IN cu_2(p_item_id) LOOP
        
            insert into temp_table(......);
           
           bom_exp1(lr_2.component_item_id);

   END LOOP;
END;

hotwish

最初由 lveamy 发布
[B]肯定有的
select DISTINCT
msi1.segment1 P_item,
to_char(b.lvl) LV,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,
b.component_quantity Component_Qty
from inv.mtl_system_items msi1,
inv.mtl_system_items msi2,
bom.bom_bill_of_materials bom,
bom.bom_operation_sequences bos,
(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 bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
and msi.segment1='10022111742'
and msi.organization_id = 2
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id in
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 2
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 = bom.bill_sequence_id
and bom.ORGANIZATION_ID =2
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
order by 2
试试这个怎么样的...呵呵... [/B]

感觉很好,可惜不能执行成功?

hotwish

楼主发了问题就不见拉,问题解决了吗?

david_liao

最初由 lveamy 发布
[B]肯定有的
select DISTINCT
msi1.segment1 P_item,
to_char(b.lvl) LV,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,
b.component_quantity Component_Qty
from inv.mtl_system_items msi1,
inv.mtl_system_items msi2,
bom.bom_bill_of_materials bom,
bom.bom_operation_sequences bos,
(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 bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
and msi.segment1='10022111742'
and msi.organization_id = 2
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id in
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 2
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 = bom.bill_sequence_id
and bom.ORGANIZATION_ID =2
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
order by 2
试试这个怎么样的...呵呵... [/B]

這個以前改過,基本上是可以用的,衹是有個別BOM導出來不是很全,如果有版本的話就比較麻煩.

hotwish

我的意思是以上的SQL SCRIPT有问题,可否修正一下,谢谢