Find Two Bills of Material (BOM) for the Same Item (BOMFDBOM) leading to ORA-01427 or SQL-02112 error (Doc ID 370841.1)

Last updated on NOVEMBER 02, 2016

Applies to:

Oracle Bills of Material - Version 11.5.10.0 and later
Information in this document applies to any platform.
FORM:BOMFDBOM.FMB - Define Bill of Material


Symptoms

You find one item has two (or more) Bill of Materials at the table level.
There are two records for the same assembly_item_id in bom_bill_of_materials but with different bill_sequence_id.
Note that the duplicates may appear as Engineering items or regular Manufactured items.

Steps

  1. Goto Bills of Material > Bills > Bills
  2. Query an existing bill of material
  3. Multiple rows display for the same item

Errors

The duplicate bills can cause multiple errors including the following:

 

Verification

You can verify if you have this issue thru the following queries.

Note: In R11.5.10, R12 and above, the bills table changed to BOM_STRUCTURES_B but the SQL-selects using BOM_BILL_OF_MATERIALS will still work. See <Note:782901.1> What Are The Differences Between Tables ...


1. Query for counts on Duplicate BOM:

select count(*)
from BOM_BILL_OF_MATERIALS
where ASSEMBLY_ITEM_ID IN
(select distinct ASSEMBLY_ITEM_ID
from BOM_BILL_OF_MATERIALS
group by ASSEMBLY_ITEM_ID,ORGANIZATION_ID,ALTERNATE_BOM_DESIGNATOR
having count(*) > 1 );


2. Query to Find list of duplicate BOMs

select ORGANIZATION_ID,ASSEMBLY_ITEM_ID,ALTERNATE_BOM_DESIGNATOR,COUNT(*)
from BOM_BILL_OF_MATERIALS
group by ORGANIZATION_ID,ASSEMBLY_ITEM_ID,ALTERNATE_BOM_DESIGNATOR
having count(*) > 1 ;


-or-

This is an alternative query that identifies the item number along with the designator and count:

SELECT
mif.item_number,
assembly_item_id,
bbom.organization_id,
alternate_bom_designator,
count(*)
FROM bom_bill_of_materials bbom, mtl_item_flexfields mif
WHERE
bbom.assembly_item_id = mif.inventory_item_id and
bbom.organization_id = mif.organization_id
GROUP BY item_number, assembly_item_id,
bbom.organization_id, alternate_bom_designator
HAVING count(*) >1;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms