SuiteQL – getting BOM information

The structure of how an item assembly is made up on a Bill of Materials (BOM), as well as having multiple revisions of that BOM can be complicated in Netsuite. I recently had a requirement to map this data out for data import.

Given the complexity of the linkage, I consider this data outside the remit of a single saved search – so opted to do it in SuiteQL instead. Below is the query, involving the bom, bomrevision and bomcomponent tables. In between these tables are the bomassemblymap and bomrevisionmap tables which provide a 1:many linkage as a BOM can have several revisions. I also use a relative date range to pull only the most recent revision

SELECT bomrev.effectivestartdate, bomrev.effectiveenddate, bomrevcom.item AS itemid, BUILTIN.DF(bomrevcom.item) AS item, bomrevcom.bomquantity AS qty
FROM bomassemblyitemmap AS bam
JOIN bom ON bom.id = bam.bom
JOIN bomrevisionbommap AS bommap ON bommap.billofmaterials = bom.id
JOIN bomrevision AS bomrev ON bomrev.id = bommap.bomrevision
JOIN bomrevisioncomponent AS bomrevcom ON bomrevcom.bomrevision = bomrev.id
WHERE bomrev.effectivestartdate <=  BUILTIN.RELATIVE_RANGES( 'TODAY',  'START')  AND (bomrev.effectiveenddate >=  BUILTIN.RELATIVE_RANGES( 'TODAY',  'START') OR bomrev.effectiveenddate IS NULL)

‘Master’ item for the BOM can be filtered by setting the bomassemblymap.assemblyitem filter to the internalid of the item.