SuiteQL: Bill of Material Components

Introduction

In this post we will discuss how we can use SuiteQL to query the components of a BOM given a specific Bom Revision ID. If you do not know the bom revision ID, you can use the Queries from the post Boms, Assemblies, and Revisions to find the bom revision ID from an assembly.

This post is only if you have enabled advanced BOMs in NetSuite.

BomRevisionComponentMember

This table contains the data for the bom components.

It contains a column called BomRevision which contains the internal ID of the bom revision that the component is associated with.

In addition this table contains the following columns that are of interest:

Column	Type	Description
BomRevision	Integer (internal id)	The bom revision that the component is associated with
item	Integer (internal id)	The item that is the component (note that this can be an assembly or an inventory item or perhaps another item type)
bomquantity	Decimal	The quantity of the component that is required
unit	Integer (internal id)	The unit of measure that the quantity is in

There are other columns in this table, but this post will just focus on getting the quantities of the components.

Let’s write some SuiteQL to get the components of a BOM revision.

SELECT
    BUILTIN.DF(BomRevisionComponentMember.BomRevision) as bomrevision,
    BUILTIN.DF(BomRevisionComponentMember.item) as item,
    BomRevisionComponentMember.bomquantity,
    BUILTIN.DF(BomRevisionComponentMember.unit) as unit
FROM
    BomRevisionComponentMember
WHERE
    BomRevisionComponentMember.BomRevision = 1234

Note that the column bomquantity is is the quantity per the unit specified on the bom revision. This contrasts with other tables in NetSuite that generally store the quantity in the base units.

If we want to get the cost of the BOM components, we need to join the item table (this assumes average costing, for more information on costing methods see this post):

SELECT
    BUILTIN.DF(BomRevisionComponentMember.BomRevision) as BomRevision,
    BUILTIN.DF(BomRevisionComponentMember.item) as item,
    BomRevisionComponentMember.bomquantity,
    BUILTIN.DF(BomRevisionComponentMember.unit) as unit,
    item.averagecost as averagecost,
    item.averagecost * BomRevisionComponentMember.bomquantity as totalcost
FROM
    BomRevisionComponentMember
    inner join item on item.id = BomRevisionComponentMember.item
WHERE
    BomRevisionComponentMember.BomRevision = 1234

But we run into a problem here.

The field item.averagecost is stored in the base unit of measure for the item. The field BomRevisionComponentMember.bomquantity is stored in the unit of measure specified on the bom revision.

So let’s say we have an item “Item 1” that has a base unit of measure LB and a cost of $5 per LB. And the bom revision has a unit of measure of Bag (50 LB) and a quantity of 2 bags.

So the true cost of the item is $5 * 50 LB * 2 bags = $500. But the query above will return $5 * 2 bags = $10.

To get the correct cost we need to convert the quantity of the component to the base unit of measure of the item. See the post Converting Units of Measure for more information on how to do this.

SELECT
    BUILTIN.DF(BomRevisionComponentMember.BomRevision) as BomRevision,
    BUILTIN.DF(BomRevisionComponentMember.item) as item,
    BomRevisionComponentMember.bomquantity,
    BUILTIN.DF(BomRevisionComponentMember.unit) as unit,
    item.averagecost as averagecostinbaseunits,
    item.averagecost * UnitsTypeUom.conversionrate as averagecostinbomunit,
    item.averagecost * BomRevisionComponentMember.bomquantity * UnitsTypeUom.conversionrate as totalcost
FROM
    BomRevisionComponentMember
    inner join item on item.id = BomRevisionComponentMember.item
    inner join UnitsTypeUom on UnitsTypeUom.internalid = BomRevisionComponentMember.unit
WHERE
    BomRevisionComponentMember.BomRevision = 1234

Note that if the BOM Unit is the same as the base unit of measure of the item, then the conversion rate will be 1.

Conclusion

In this post we discussed how we can use SuiteQL to query the components of a BOM given a specific Bom Revision ID. NetSuite Costed BOM Inquiries don’t if you use average costing, so this can be an alternative.

This does not traverse the BOM hierarchy, and that capability is missing from SuiteQL. To traverse the BOM hierarchy we must use SuiteScript.

This will be discussed tomorrow in the next post and last post in the SuiteQL series.