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;

转载于:https://www.cnblogs.com/st-sun/p/3781930.html