ScottDanesi.com

Hey Everyone,

I know this is not the most exciting post about new pinball stuff, but I just wanted to share this code with the world that I created to get around a huge limitation in Oracle NetSuite.

We recently upgraded our backend system to NetSuite Manufacturing and it has been really great so far. I am a huge data nerd as many of you know. The one major issue we have been having is trying to get a nice report of the Bill of Material (BOM) components within our Assemblies with the current and average costs. We use these reports to make sure that we are not under or overcharging for parts that we assemble in house. After looking around online and talking to support, it was clear there was no solution to show this properly. You can see what the last build price is, but that is not what we need. So I took it upon myself to write a direct SQL query into the database to show what we need. I feel that this is so valuable for the community that I wanted to post it up here as a starting point for others.

In order to run direct SQL in NetSuite, you will need to first install the free SuiteQL Query Tool from Tim Dietrich from the link below:

https://timdietrich.me/netsuite-suitescripts/suiteql-query-tool/

Once you install and configure this script within your NetSuite environment, you will need to copy the code below into the query window. Once you have that code ready to go, you will need to update 2 places in the code that reference the BOM Revision name. You must copy the BOM Revision name from your system exactly for this to work. Once the 2 spots are updated, you can hit run and enjoy the BOM analysis!

/********************************************************************************
Title:			Pinball Life NetSuite Assembly Cost Analysis SuiteQL Query

Created: 		2023-03-22 (Scott Danesi)

Description: 	Basic BOM Revision costing report used to show average cost 
				analysis on specific BOM Revisions in NetSuite.

Instructions:	Replace the 2 revision names in the code below to the
				exact revision name that you would like to run the report for.
				These revision names must be the full revision name.  If both
				revision locations are not replaced, the report will give
				incorrect results.
********************************************************************************/

SELECT
	t2.fullname as "assembly name",
	t2.displayName as "assembly description",
	bomRevision.name as "bom revision",
	item.itemId as "component Name",
	BomRevisionComponent.description as "description",
	BomRevisionComponent.bomQuantity as "quantity",
	to_char( item.cost, '$9,999.9999' ) as "cost",
	to_char( CASE WHEN item.averageCost = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as "average cost",
	to_char( BomRevisionComponent.bomQuantity * CASE WHEN item.averageCost = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as "avg cost total",
	to_char( CASE WHEN item.lastPurchasePrice = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as "last purchase price",
	to_char( BomRevisionComponent.bomQuantity * CASE WHEN item.lastPurchasePrice = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as "last purchase total",
	t1.altName  as "preferred vendor" 
FROM
	BomRevisionComponent
	LEFT JOIN item ON BomRevisionComponent.item = item.id
	LEFT JOIN bomRevision ON BomRevisionComponent.bomrevision = bomRevision.id
	LEFT JOIN
		(
			SELECT
				itemVendor.item,
				itemVendor.preferredVendor,
				itemVendor.vendor,
				Vendor.altname
			FROM
				itemVendor
				LEFT JOIN Vendor ON itemVendor.vendor = Vendor.id
			WHERE
				itemVendor.preferredVendor = 'T'
		) as t1
		ON t1.item = item.id
	LEFT JOIN 
		(
			SELECT
				item.fullName,
				item.displayName,
				item.id,
				bomRevision.name
			FROM
				item
				JOIN bomAssembly ON item.id = bomAssembly.assembly
				JOIN bom ON bomAssembly.billofmaterials = bom.id
				JOIN bomRevision ON bomRevision.billofmaterials = bom.id
		) as t2
		ON t2.name = bomRevision.name
WHERE bomRevision .name = '0199-0699_LEFT-BOM-REV-' 			/************ <--- Change this to the exact BOM Revision that you want to see. ******************/

UNION

SELECT
	NULL as "assembly name",
	NULL as "assembly description",
	NULL as "bom revision",
	NULL as "component name",
	'TOTAL COST' as "description",
	NULL  as "quantity",
	NULL  as "cost",
	NULL  as "average cost",
	to_char( SUM(BomRevisionComponent.bomQuantity * CASE WHEN item.averageCost = 0 THEN item.cost ELSE item.averageCost END), '$9,999.99' ) as "avg cost total",
	NULL as "last Purchase Price",
	to_char( SUM(BomRevisionComponent.bomQuantity * CASE WHEN item.lastPurchasePrice = 0 THEN item.cost ELSE item.averageCost END), '$9,999.99' ) as "last purchase total",
	NULL as "preferred vendor" 
FROM
	BomRevisionComponent
LEFT JOIN item ON BomRevisionComponent.item = item.id
LEFT JOIN bomRevision ON BomRevisionComponent.bomrevision = bomRevision.id
WHERE bomRevision .name = '0199-0699_LEFT-BOM-REV-'			/************ <--- Change this to the exact BOM Revision that you want to see. ******************/

Please keep in mind, this is the first revision of this code that I made, it is not the most efficient or the cleanest, but it is a good starting point if you need to see this data. I really hope this helps someone out there as much as it did for me. Please feel free to reach out to me if you have any suggestions, or see any errors in my code. Enjoy!