Introduction
In this post we will discuss getting the bill of material components recursively for all levels of an assembly that contains other subassemblies. Please see the two previous posts about getting the BOM revision and getting the BOM components for a single level. This is similar to the Costed Bom Inquiries in NetSuite, but NetSuite doesn’t allow you to use that report for assemblies with average costing. This post will offer an alternative solution to cost BOMs using SuiteQL and SuiteScript.
Because the sql cte WITH RECURSIVE is not supported in SuiteQL, we will have to use a recursive function in SuiteScript.
A few disclaimers about this post:
- It assumes that you are using average cost
- It uses the item.itemid instead of the internal id for readability. In practice you should use the internal id as it is faster.
- It assumes that all assemblies have an active bom revision with a Master Default BOM. If you have multiple boms for different locations, you will need to modify the query and pass in the location as a parameter.
- It isn’t optimized for performance. It will work for a single assemlby but when I push it to production I can make one single query to get all components and then use javascript to pull whats needed.
- It needs better error handling
Example
Imagine that we have an assembly to make a Table. That table has “Legs” and a “Top” as components. “Legs” are made up of “Wood” and “Screws” and contains a “Base” which is made up of “Base Plate” and “Base Screws” Here is a diagram of the assembly:
1
2
3
4
5
6
7
8
Table
Legs (4)
Wood (2)
Screws (4)
Base (1)
Base Plate (1)
Base Screws (4)
Top (1)
Now suppose the cost of the raw materials is as follows:
| Item | Cost |
|---|---|
| Wood | $1.00 |
| Screws | $0.10 |
| Base Plate | $2.00 |
| Base Screws | $0.10 |
| Top | $10.00 |
Then the cost of the assemblies is:
| Item | Cost | Equation |
|---|---|---|
| Base | $2.40 | 1 (base plate) * $2.00 + 4 (base screws) * $0.10 |
| Leg | $4.80 | 1 (base) * $2.40 + 2 (wood) * $1.00 + 4 (screws) * $0.10 |
| Table | $29.20 | 1 (top) * $10.00 * 4 (legs) * $4.80 |
The result of the function we’ll write will be a javascript object with the following properties:
| Property | Description |
|---|---|
| item | The itemid of the component |
| bomquantity | The quantity of the component in the bom |
| quantitytoplevel | The quantity of the component that makes it to the top level assembly |
| cost | The total cost of the component for the top level assembly |
| costperunit | The cost per single unit of the component |
| parentid | The itemid of the immidiet parent assembly |
| itemtype | The type of the item (Assembly or InvtPart) |
Here is an example of the result:
1
2
3
4
5
6
7
8
9
[
{ "item": "Base Plate", "bomquantity": 1, "quantitytoplevel": 4, "cost": 8, "costperunit": 2, "parentid": "Base", "level": 2, "itemtype": "InvtPart" },
{ "item": "Base Screws", "bomquantity": 4, "quantitytoplevel": 16, "cost": 1.6, "costperunit": 0.1, "parentid": "Base", "level": 2, "itemtype": "InvtPart" },
{ "item": "Base", "bomquantity": 1, "quantitytoplevel": 4, "cost": 9.6, "costperunit": 2.4, "parentid": "Leg", "level": 1, "itemtype": "Assembly" },
{ "item": "Screws", "bomquantity": 4, "quantitytoplevel": 16, "cost": 1.6, "costperunit": 0.1, "parentid": "Leg", "level": 1, "itemtype": "InvtPart" },
{ "item": "Wood", "bomquantity": 2, "quantitytoplevel": 8, "cost": 8, "costperunit": 1, "parentid": "Leg", "level": 1, "itemtype": "InvtPart" },
{ "item": "Leg", "bomquantity": 4, "quantitytoplevel": 4, "cost": 19.2, "costperunit": 4.8, "parentid": "Table", "level": 0, "itemtype": "Assembly" },
{ "item": "Top", "bomquantity": 1, "quantitytoplevel": 1, "cost": 10, "costperunit": 10, "parentid": "Table", "level": 0, "itemtype": "InvtPart" }
]
And here is an example report that can be created from the result: 
Let’s first write all our helper functions
This function will get the average cost of an item
1
2
3
4
5
6
function getAverageCost(item) {
return query.runSuiteQL(`
SELECT averagecost
from item where item.itemid = '${item}'
`)[0].averagecost;
}
This function will get the currently active bom revision of an item (note that location is not considered, it will only get the master default bom or fail if there is no master default):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
function getCurrentBomRevision(item) {
return query.runSuiteQL(`
SELECT
BomRevision.id
FROM BomAssembly
INNER JOIN bom on BomAssembly.billofmaterials = Bom.id
INNER JOIN BomRevision on BomRevision.billofmaterials = Bom.id
INNER JOIN item on item.id = BomAssembly.assembly
WHERE
item.itemid = '${item}'
and
(BomAssembly.masterdefault = 'T')
AND (
(BomRevision.effectivestartdate <= SYSDATE and BomRevision.effectiveenddate is null) or
(BomRevision.effectivestartdate <= SYSDATE and BomRevision.effectiveenddate >= SYSDATE)
)
`).asMappedResults()[0].id;
}
This function will get the bom components of an item
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
function getBomComponents(item) {
//using getCurrentBomRevision function
const currentBomRevision = getCurrentBomRevision(item);
return query.runSuiteQL(`
SELECT
item.itemid as item,
BomRevisionComponentMember.bomquantity,
item.itemtype as itemtype
FROM
BomRevisionComponentMember
inner join item on item.id = BomRevisionComponentMember.item
WHERE
BomRevisionComponentMember.BomRevision = ${currentBomRevision}
`).asMappedResults();
}
The Recursive Function
Now that we have all our helper functions, we can write the recursive function to get all the bom components of an item.
We will need to pass the following special parameters to the function:
- item - the item whose bom components we want to get
- level - the level of the bom component, this will increase by 1 for each level
- factor - the factor by which the bom quantity will be multiplied by, this will be the quantity of the parent item * whatever factor it itself has
So for example when the recursive function is called for Legs, the level will be 2, as all components of Legs are at level 2, and the factor will be 4 as all components of legs are multiplied by 4 to get the quantity of the raw material required for the entire table.
Line 16 is a bit of a mystery, but it’s just a way to get the cost of the components of an assembly without double counting the subcomponents. So for the subcomponent of Legs, we want to count the cost of the Base, Wood and Screws, but not the Base Plate and Base Screws, as they are already counted in the cost of the Base.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
function getBomComponentsRecursive(item, level = 1, factor = 1) {
const components = [];
const topLevelComponents = getBomComponents(item);
topLevelComponents.forEach((component) => {
var costperunit = 0;
if (component.itemtype === "Assembly") {
const subComponents = getBomComponentsRecursive(
component.item,
level + 1,
component.bomquantity * factor
);
costperunit = subComponents.reduce((acc, subComponent) => {
acc += subComponent.parentid == component.item ? subComponent.costperunit * subComponent.bomquantity : 0;
return acc;
}, 0);
components.push(...subComponents);
} else {
costperunit = getAverageCost(component.item);
}
const bomcost = costperunit * component.bomquantity * factor;
const topLevelQuantity = component.bomquantity * factor;
components.push({
item: component.item,
bomquantity: component.bomquantity,
quantitytoplevel: topLevelQuantity,
cost: bomcost,
costperunit: costperunit,
parentid: item,
level: level,
itemtype: component.itemtype,
});
});
return components;
}
Here is a link to a SuiteScript custom module that implements the above function
Conclusion
This function forms the basis for a very common report I write for clients to generate a costed bill of materials for average cost items. A few things might need to be modified such as location specific BOMs, units of measure conversions, etc. but the basic structure is the same.
Depending on the size and levels of the BOMs in the specific account, you might need to write unwieldy code to pull the data from tables before running the recursive function.
Right now the function is making separate queries to the BomRevisionComponentMember table for each item, but we can pull all the data from the table into a single array and then filter it for each item.
In the next and last post of this series we will look at bypassing the 5000 row limit of SuiteQL.