Oracle Ebs-sql (BOM-19): Main BOM interchange with alternative BOM. SQL

Alternate BOM and main BOM convert each other

bom:1-01-27-211 Sub-pieces: 1-01-27-416 id:2202

BOM Substitution: Replacement 0001 sub-pieces: 1-01-26-204 id:2205

--1, wip:1202

--2, after finishing WIP 1202, 1-01-27-211 Cost:

--3, main BOM and alternative BOM interchange

--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;

---------------------------------------------------------------------------------------

/*--main BOM and alternative BOM interchange-script */

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): = ' alternative 0001 ';

Begin

--Modify the main 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;

--Modify Alternative 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;

--Modify the main BOM as an alternative 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