Oracle EBS-SQL (BOM-19):主BOM与替代BOM互换.sql
替代BOM与主BOM互相转换
BOM: 1-01-27-211 子件：1-01-27-416 ID：2202
BOM替代项：替代0001 子件： 1-01-26-204 ID：2205
--1、WIP：1202
--2、做完WIP 1202后，1-01-27-211 成本：
--3、主BOM与替代BOM互换
--4、WIP:1203
-------------------------------------------------------
select msib.segment1, b.*
from bom_bill_of_materials b,
mtl_system_items_b msib
where b.ASSEMBLY_ITEM_ID= msib.inventory_item_id
and b.ORGANIZATION_ID = msib.organization_id
and b.ALTERNATE_BOM_DESIGNATOR is not null ;
---------------------------------------------------------------------------
select b.BILL_SEQUENCE_ID,
b.COMMON_BILL_SEQUENCE_ID,
b.SOURCE_BILL_SEQUENCE_ID,
b.ALTERNATE_BOM_DESIGNATOR,
b.ASSEMBLY_ITEM_ID
from bom_bill_of_materials b
where b.ASSEMBLY_ITEM_ID=1045
and b.ORGANIZATION_ID=X
-----------------------------------------------------------------------------
select *
from bom_inventory_components c
where c.bill_sequence_id=637;
---------------------------------------------------------------------------------------
/*--主BOM与替代BOM互换--脚本*/
declare
v_organization_id number :=X;
v_item_id number := 1045;
b_pra_bom_seq_id number := 2205;
b_bom_seq_id number := 2202;
b_bom_alternate varchar2(80) := '替代0001';
begin
--修改主BOM
update bom_structures_b b
set b.bill_sequence_id= -1,
b.common_bill_sequence_id = -1,
b.source_bill_sequence_id = -1
where b.organization_id= v_organization_id
and b.assembly_item_id=v_item_id
and b.alternate_bom_designator is null;
--修改替代BOM
update bom_structures_b b
set b.bill_sequence_id= b_pra_bom_seq_id,
b.common_bill_sequence_id = b_pra_bom_seq_id,
b.source_bill_sequence_id = b_pra_bom_seq_id
where b.organization_id= v_organization_id
and b.assembly_item_id=v_item_id
and b.alternate_bom_designator = b_bom_alternate;
--修改主BOM为替代BOM
update bom_structures_b b
set b.bill_sequence_id= b_bom_seq_id,
b.common_bill_sequence_id = b_bom_seq_id,
b.source_bill_sequence_id = b_bom_seq_id
where b.organization_id= v_organization_id
and b.assembly_item_id=v_item_id
and b.alternate_bom_designator is null;
end;
