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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.