SuiteQL: Boms, Assemblies, and Revisions

Introduction

This post assumes you are using advanced BOMs.

Before tackling how we can use SuiteQL to query BOM components, we need to discuss how we can deduce the active BOM Revision(s) for a given item with SuiteQL.

In NetSuite, Assemblies can have multiple BOMs associated with them. Each BOM can be marked as “Master Default” or can be marked as “Default for Location”. There can be multiple BOMs without either of these fields populated.

Each BOM can have multiple revisions. The active revision is the one that is marked as “Active” and the current date is between the “Start Date” and “End Date” fields on the BOM revision or the current date is more than the start date and there is no effective end date.

There are three tables that we need to discuss in order to understand how NetSuite deduces the active BOM Revision(s) for a given item:

• BomAssembly
• BomRevision
• BomAvailableLocationMap
• Bom

BomAssembly

This table contains the boms selected for a given item on the “Manufacturing” tab of the item record.

Column	Description	Type
canbemaster	Indicates whether the item can be used as a master default in an item	Boolean
masterdefault	Indicates whether the item is the master default in an item	Boolean
billofmaterials	The Bill of Materials selected	Integer (Internal ID)
assembly	The Assembly	Integer (Internal ID)

BomAvailableLocationMap

This table represents the locations that a given bom is available for use in. This is also selected on the “Manufacturing” tab of the item record.

This table contains only two columns:

Column	Description	Type
bom	The Bill of Materials selected	Integer (Internal ID)
location	The location that the bom is available for use in	Integer (Internal ID)

Bom

This table contains the data for the bom itself.

There are many columns such as name, usedonassembly, etc. The only column that we are concerned with is the column BomRevision.

This column contains a comma separated list of the internal IDs of the bom revisions that are associated with the bom. In terms of figuring out which bom revision is active, we need to look at the BomRevision table.

BomRevision

This table contains the data for the bom revision record. It does not contain the components of the bom revision, that is contained in the BomRevisionComponentMember table which we will discuss in a separate post.

The columns relevant to deducing the active bom revision are:

Column	Description	Type
bom	The Bill of Materials	Integer (Internal ID)
effectivestartdate	The date that the bom revision becomes active	Date
effectiveenddate	The date that the bom revision becomes inactive	Date

Writing the query

We’ll assume that we have the internal ID of the location that we want to get the active bom revision for. We will use ${locationid} to represent this value.

First we’ll join the tables that we’ll be needing together Note(we won’t join the BomAvailableLocationMap if we have a masterdefault):

FROM BomAssembly
inner join bom on BomAssembly.billofmaterials = Bom.id
inner join BomRevision on BomRevision.billofmaterials = Bom.id
LEFT JOIN BomAvailableLocationMap on BomAvailableLocationMap.bom = Bom.id and Bom.masterdefault = 'F'

Then we’ll filter the results to only include boms that are marked as the master default or are available for the location we are interested in:

WHERE (BomAssembly.masterdefault = 'T' OR BomAvailableLocationMap.location = ${locationid})

Then we’ll filter the results to only include boms that are active for the current date:

AND (
    (BomRevision.effectivestartdate <= SYSDATE AND BomRevision.effectiveenddate IS NULL) OR
    (BomRevision.effectivestartdate <= SYSDATE AND BomRevision.effectiveenddate >= SYSDATE)
    )

Putting it all together:

FROM
  BomAssembly
  INNER JOIN Bom ON BomAssembly.billofmaterials = Bom.id
  INNER JOIN BomRevision ON BomRevision.billofmaterials = Bom.id
  LEFT JOIN BomAvailableLocationMap ON BomAvailableLocationMap.bom = Bom.id
  AND BomAssembly.masterdefault = 'F'
WHERE
  (
    BomAssembly.masterdefault = 'T'
    OR BomAvailableLocationMap.location = ${locationid}
    AND (
      ( BomRevision.effectivestartdate <= SYSDATE AND BomRevision.effectiveenddate IS NULL) OR
      ( BomRevision.effectivestartdate <= SYSDATE AND BomRevision.effectiveenddate >= SYSDATE)
    )
  )

Now we can select the fields that we want to return (make sure to replace ${locationid} with the internal ID of the location that you want to get the active bom revision for):

SELECT
  BUILTIN.DF(BomAssembly.assembly) AS itemid,
  Bom.name AS bomname,
  BomRevision.name AS BomRevisionname
FROM
  BomAssembly
  INNER JOIN Bom ON BomAssembly.billofmaterials = Bom.id
  INNER JOIN BomRevision ON BomRevision.billofmaterials = Bom.id
  LEFT JOIN BomAvailableLocationMap ON BomAvailableLocationMap.bom = Bom.id
  AND BomAssembly.masterdefault = 'F'
WHERE
  (
    BomAssembly.masterdefault = 'T'
    OR BomAvailableLocationMap.location = ${locationid}
    AND (
      ( BomRevision.effectivestartdate <= SYSDATE AND BomRevision.effectiveenddate IS NULL) OR
      ( BomRevision.effectivestartdate <= SYSDATE AND BomRevision.effectiveenddate >= SYSDATE)
    )
  )

Conclusion

Getting the active bom revision for a given item is a bit more complicated than it seems at first glance. Using a saved search may be simpler for your use case.

I use it primarily when I need to get the components which I usually do with SuiteQL.

This is discussed in the next post