Find Two Bills of Material (BOM) for the Same Item (BOMFDBOM) leading to ORA-01427 or SQL-02112 error
Last updated on NOVEMBER 02, 2016
Applies to:Oracle Bills of Material - Version 18.104.22.168 and later
Information in this document applies to any platform.
FORM:BOMFDBOM.FMB - Define Bill of Material
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.
- Goto Bills of Material > Bills > Bills
- Query an existing bill of material
- Multiple rows display for the same item
The duplicate bills can cause multiple errors including the following:
- In BOM > routings > operations resources:
ORA-01427: Single row sub-query returns more than one row
- In WIP > Discrete Jobs:
bmlslx_explode_single_level SQL-02112 select .. into returns too many rows error.
- If applying <Patch 5239326> and <Patch 5462432>, the creation of the index BOM_STRUCTURES_B_U1 fails with an error like the following:
Failed to CREATE UNIQUE INDEX BOM.BOM_STRUCTURES_B_U1 ON BOM.BOM_STRUCTURES_B
You can verify if you have this issue thru the following queries.
1. Query for counts on Duplicate BOM:
where ASSEMBLY_ITEM_ID IN
(select distinct ASSEMBLY_ITEM_ID
group by ASSEMBLY_ITEM_ID,ORGANIZATION_ID,ALTERNATE_BOM_DESIGNATOR
having count(*) > 1 );
2. Query to Find list of duplicate BOMs
group by ORGANIZATION_ID,ASSEMBLY_ITEM_ID,ALTERNATE_BOM_DESIGNATOR
having count(*) > 1 ;
This is an alternative query that identifies the item number along with the designator and count:
FROM bom_bill_of_materials bbom, mtl_item_flexfields mif
bbom.assembly_item_id = mif.inventory_item_id and
bbom.organization_id = mif.organization_id
GROUP BY item_number, assembly_item_id,
HAVING count(*) >1;
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms