Introduction
This post is the first in a series of posts that will cover a specific use case for SuiteQL. If you are not familiar with SuiteQL, I recommend reading the SuiteQL Introduction series first.
The following post assumes you have advanced bills of materials set up in NetSuite and are not using wip for your work orders.
Business Use Case
The business use case is as follows. When creating a work order, NetSuite will populate the work order with the default bill of materials for the assembly. See the Implementation: Advanced Bill of Materials post for more information on how to set up advanced bills of materials in NetSuite.
Whether it was populated by the default BOM or by the user selecting a BOM, the work order will then populate the components of the BOM into the work order. The user can add and remove components from the work order, and also change the quantity of the components.
When the work order is transformed into an assembly build, the user can then edit the quantity once more on the assembly build transaction (adding or removing components is not allowed). The business use case is to calculate the variance between the work order, assembly build, and the bill of materials. We’ll assume that you only have the work order id (1234) and want to calculate the variance.
Getting the Components
Getting the components is not as simple as you might think. We can query the work order, assembly build, or BOM for the components, but because the user can add and remove components to the work order, if we choose either of them we might be missing components. As such, we will use a UNION query to get all of the components from the BOM and the work order. We don’t need to worry about the assembly build because adding and removing components is not supported.
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH allitems AS (
SELECT
WorkorderLines.item AS item
FROM TransactionLine AS WorkorderLines
WHERE WorkorderLines.transaction = 1234
and WorkorderLines.mainline = 'F'
UNION
SELECT
BomRevisionComponentMember.item AS item,
FROM BomRevisionComponentMember
INNER JOIN Transaction ON Transaction.id = 1234
AND Transaction.billofmaterialsrevision = BomRevisionComponentMember.bomrevision
)
The UNION will ensure that we get a distinct list of all the components from the BOM and the work order.
Getting the Quantities
For the purposes of readability I’m going to split this into three CTEs, for the work order, BOM revision, and for the assembly build. Because transaction lines have a negative quantity for the work order and the assembly build, we’ll multiply the quantity by -1 to get the positive quantity.
First the Work Order (we use mainline = 'F' to exclude the assembly item):
1
2
3
4
5
6
7
8
WITH WorkorderLines AS (
SELECT
WorkorderLines.item AS item,
WorkorderLines.quantity * -1 AS quantity
FROM TransactionLine AS WorkorderLines
WHERE WorkorderLines.transaction = 1234
and WorkorderLines.mainline = 'F'
)
This will give us the quantity in the base unit of measure of the item.
Now the assembly build:
1
2
3
4
5
6
7
8
9
WITH AssemblyBuildLines AS (
SELECT
AssemblyBuildLines.item AS item,
AssemblyBuildLines.quantity * -1 AS quantity
FROM TransactionLine AS AssemblyBuildLines
WHERE AssemblyBuildLines.createdFROM = 1234
and AssemblyBuildLines.mainline = 'F'
)
This again will give us the quantity in the base unit of measure of the item.
For the BOM revision, it’s a bit more involved. We need to multiply the quantity on the BOM revision by the quantity for the work order. We can get the quantity by joining the main line of the work order to the BOM revision.
1
2
3
4
5
6
7
8
9
WITH BomRevisionLines as (
SELECT
BomRevisionComponentMember.item as item,
BomRevisionComponentMember.quantity * transactionline.quantity as quantity
FROM BomRevisionComponentMember
INNER JOIN Transaction on Transaction.id = 1234
AND Transaction.billofmaterialsrevision = BomRevisionComponentMember.bomrevision
INNER JOIN transactionline on transactionline.transaction = transaction.id and transactionline.mainline = 'T'
)
This will give us the quantity in the units of measure specified in the BOM revision.
To make it simpler to calculate the variance, we will convert the BOM revision quantities to the base unit of measure. See SuiteQL: Units Of Measure for more information.
1
2
3
4
5
6
7
8
9
10
WITH BomRevisionLines AS (
SELECT
BomRevisionComponentMember.item AS item,
BomRevisionComponentMember.quantity * TransactionLine.quantity * UnitsTypeUom.conversionrate AS quantity
FROM BomRevisionComponentMember
INNER JOIN Transaction ON Transaction.id = 1234
AND Transaction.billofmaterialsrevision = BomRevisionComponentMember.bomrevision
INNER JOIN UnitsTypeUom ON UnitsTypeUom.internalid = BomRevisionComponentMember.units
INNER JOIN TransactionLine ON TransactionLine.transaction = transaction.id and TransactionLine.mainline = 'T'
)
Now we have the bom quantities in the base unit of measure.
Calculating the Variance
Assuming we all CTEs loaded into the query, we can now calculate the variance. (I omitted the CTEs for brevity; the full query is below.)
1
2
3
4
5
6
7
8
9
10
(CTEs)
SELECT
AllItems.item as item,
WorkOrderLines.quantity as workorderquantity,
AssemblyBuildLines.quantity as assemblybuildquantity,
BomRevisionLines.quantity as bomquantity,
FROM AllItems
LEFT JOIN WorkOrderLines on WorkOrderLines.item = allitems.item
LEFT JOIN AssemblyBuildLines on AssemblyBuildLines.item = allitems.item
LEFT JOIN BomRevisionLines on BomRevisionLines.item = allitems.item
Now the report will look something like this:
| Item | Work Order Quantity | Assembly Build Quantity | BOM Quantity |
|---|---|---|---|
| 111 (Internal ID) | 13 | 14 | 10 |
| 122 (Internal ID) | 20 | 20 | 25 |
| 133 (Internal ID) | 11 | 10 | 0 |
As we can see the report is pretty bare.
The column Item is the internal id, and the quantities are in the base unit of measure.
Let’s spice things up.
Spicing Things Up
Let’s get the item name and convert the quantities to the consumption unit of measure on the item record. We’ll also replace the columns Work Order Quantity and Assembly Build Quantity to be a variance column. We’ll add a column with assembly cost (assuming average cost).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
(CTEs)
SELECT
item.itemid as itemid,
(bomrevisionlines.quantity)/ConsumptionUom.conversionrate as bomquantity,
(bomrevisionlines.quantity - workorderlines.quantity)/ConsumptionUom.conversionrate as workordervariance,
(bomrevisionlines.quantity - assemblylines.quantity)/ConsumptionUom.conversionrate as assemblyvariance,
ConsumptionUom.pluralAbbreviation as consumptionunit,
(assemblylines.quantity * item.averagecost)/ConsumptionUom.conversionrate as assemblycost
FROM allitems
LEFT JOIN workorderlines on workorderlines.item = allitems.item
LEFT JOIN assemblybuildlines on assemblybuildlines.item = allitems.item
LEFT JOIN bomrevisionlines on bomrevisionlines.item = allitems.item
INNER JOIN Item on Item.id = allitems.item
INNER JOIN UnitsTypeUom as ConsumptionUom on ConsumptionUom.internalid = Item.consumptionunit
This will give us a report that looks like this:
| Item | BOM Quantity | Work Order Variance | Assembly Variance | Consumption Unit | Assembly Cost |
|---|---|---|---|---|---|
| Component 1 | 10 | 3 | 4 | Bag (50 LB) | $10 |
| Component 2 | 25 | -5 | -5 | KG | $25 |
| Component 3 | 0 | 11 | 10 | LB | $12 |
We’ll have to wrap some columns in COALESCE to make sure we don’t get any null values.
Here is the full query
Full Query
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
48
49
50
WITH allitems AS (
SELECT
WorkorderLines.item AS item
FROM TransactionLine AS WorkorderLines
WHERE WorkorderLines.transaction = 1234
and WorkorderLines.mainline = 'F'
UNION
SELECT
BomRevisionComponentMember.item AS item,
FROM BomRevisionComponentMember
INNER JOIN Transaction ON Transaction.id = 1234
AND Transaction.billofmaterialsrevision = BomRevisionComponentMember.bomrevision
), WorkorderLines AS (
SELECT
WorkorderLines.item AS item,
WorkorderLines.quantity * -1 AS quantity
FROM TransactionLine AS WorkorderLines
WHERE WorkorderLines.transaction = 1234
and WorkorderLines.mainline = 'F'
), AssemblyBuildLines AS (
SELECT
AssemblyBuildLines.item AS item,
AssemblyBuildLines.quantity * -1 AS quantity
FROM TransactionLine AS AssemblyBuildLines
WHERE AssemblyBuildLines.createdFROM = 1234
and AssemblyBuildLines.mainline = 'F'
), BomRevisionLines AS (
SELECT
BomRevisionComponentMember.item AS item,
BomRevisionComponentMember.quantity * TransactionLine.quantity * UnitsTypeUom.conversionrate AS quantity
FROM BomRevisionComponentMember
INNER JOIN Transaction ON Transaction.id = 1234
AND Transaction.billofmaterialsrevision = BomRevisionComponentMember.bomrevision
INNER JOIN UnitsTypeUom ON UnitsTypeUom.internalid = BomRevisionComponentMember.units
INNER JOIN TransactionLine ON TransactionLine.transaction = transaction.id and TransactionLine.mainline = 'T'
)
SELECT
item.itemid AS itemid,
(COALESCE(BomRevisionLines.quantity, 0))/ConsumptionUom.conversionrate AS bomquantity,
(COALESCE(BomRevisionLines.quantity, 0) - COALESCE(WorkorderLines.quantity, 0))/ConsumptionUom.conversionrate AS workordervariance,
(COALESCE(BomRevisionLines.quantity, 0) - COALESCE(AssemblyBuildLines.quantity, 0))/ConsumptionUom.conversionrate AS assemblyvariance,
ConsumptionUom.pluralAbbreviation AS consumptionunit,
(COALESCE(AssemblyBuildLines.quantity, 0) * item.averagecost)/ConsumptionUom.conversionrate AS assemblycost
FROM allitems
INNER JOIN Item ON Item.id = allitems.item
INNER JOIN UnitsTypeUom AS ConsumptionUom ON ConsumptionUom.internalid = Item.consumptionunit
LEFT JOIN WorkorderLines ON workorderlines.item = allitems.item
LEFT JOIN AssemblyBuildLines ON AssemblyBuildLines.item = allitems.item
LEFT JOIN BomRevisionLines ON bomrevisionlines.item = allitems.item