https:\/\/timdietrich.me\/netsuite-suitescripts\/suiteql-query-tool\/<\/a><\/p>\n\n\n\nOnce 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!<\/p>\n\n\n\n
\/********************************************************************************\r\nTitle:\t\t\tPinball Life NetSuite Assembly Cost Analysis SuiteQL Query\r\n\r\nCreated: \t\t2023-03-22 (Scott Danesi)\r\n\r\nDescription: \tBasic BOM Revision costing report used to show average cost \r\n\t\t\t\tanalysis on specific BOM Revisions in NetSuite.\r\n\r\nInstructions:\tReplace the 2 revision names in the code below to the\r\n\t\t\t\texact revision name that you would like to run the report for.\r\n\t\t\t\tThese revision names must be the full revision name. If both\r\n\t\t\t\trevision locations are not replaced, the report will give\r\n\t\t\t\tincorrect results.\r\n********************************************************************************\/\r\n\r\nSELECT\r\n\tt2.fullname as \"assembly name\",\r\n\tt2.displayName as \"assembly description\",\r\n\tbomRevision.name as \"bom revision\",\r\n\titem.itemId as \"component Name\",\r\n\tBomRevisionComponent.description as \"description\",\r\n\tBomRevisionComponent.bomQuantity as \"quantity\",\r\n\tto_char( item.cost, '$9,999.9999' ) as \"cost\",\r\n\tto_char( CASE WHEN item.averageCost = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as \"average cost\",\r\n\tto_char( BomRevisionComponent.bomQuantity * CASE WHEN item.averageCost = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as \"avg cost total\",\r\n\tto_char( CASE WHEN item.lastPurchasePrice = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as \"last purchase price\",\r\n\tto_char( BomRevisionComponent.bomQuantity * CASE WHEN item.lastPurchasePrice = 0 THEN item.cost ELSE item.averageCost END, '$9,999.9999' ) as \"last purchase total\",\r\n\tt1.altName as \"preferred vendor\" \r\nFROM\r\n\tBomRevisionComponent\r\n\tLEFT JOIN item ON BomRevisionComponent.item = item.id\r\n\tLEFT JOIN bomRevision ON BomRevisionComponent.bomrevision = bomRevision.id\r\n\tLEFT JOIN\r\n\t\t(\r\n\t\t\tSELECT\r\n\t\t\t\titemVendor.item,\r\n\t\t\t\titemVendor.preferredVendor,\r\n\t\t\t\titemVendor.vendor,\r\n\t\t\t\tVendor.altname\r\n\t\t\tFROM\r\n\t\t\t\titemVendor\r\n\t\t\t\tLEFT JOIN Vendor ON itemVendor.vendor = Vendor.id\r\n\t\t\tWHERE\r\n\t\t\t\titemVendor.preferredVendor = 'T'\r\n\t\t) as t1\r\n\t\tON t1.item = item.id\r\n\tLEFT JOIN \r\n\t\t(\r\n\t\t\tSELECT\r\n\t\t\t\titem.fullName,\r\n\t\t\t\titem.displayName,\r\n\t\t\t\titem.id,\r\n\t\t\t\tbomRevision.name\r\n\t\t\tFROM\r\n\t\t\t\titem\r\n\t\t\t\tJOIN bomAssembly ON item.id = bomAssembly.assembly\r\n\t\t\t\tJOIN bom ON bomAssembly.billofmaterials = bom.id\r\n\t\t\t\tJOIN bomRevision ON bomRevision.billofmaterials = bom.id\r\n\t\t) as t2\r\n\t\tON t2.name = bomRevision.name\r\nWHERE bomRevision .name = '0199-0699_LEFT-BOM-REV-' \t\t\t\/************ <--- Change this to the exact BOM Revision that you want to see. ******************\/\r\n\r\nUNION\r\n\r\nSELECT\r\n\tNULL as \"assembly name\",\r\n\tNULL as \"assembly description\",\r\n\tNULL as \"bom revision\",\r\n\tNULL as \"component name\",\r\n\t'TOTAL COST' as \"description\",\r\n\tNULL as \"quantity\",\r\n\tNULL as \"cost\",\r\n\tNULL as \"average cost\",\r\n\tto_char( SUM(BomRevisionComponent.bomQuantity * CASE WHEN item.averageCost = 0 THEN item.cost ELSE item.averageCost END), '$9,999.99' ) as \"avg cost total\",\r\n\tNULL as \"last Purchase Price\",\r\n\tto_char( SUM(BomRevisionComponent.bomQuantity * CASE WHEN item.lastPurchasePrice = 0 THEN item.cost ELSE item.averageCost END), '$9,999.99' ) as \"last purchase total\",\r\n\tNULL as \"preferred vendor\" \r\nFROM\r\n\tBomRevisionComponent\r\nLEFT JOIN item ON BomRevisionComponent.item = item.id\r\nLEFT JOIN bomRevision ON BomRevisionComponent.bomrevision = bomRevision.id\r\nWHERE bomRevision .name = '0199-0699_LEFT-BOM-REV-'\t\t\t\/************ <--- Change this to the exact BOM Revision that you want to see. ******************\/<\/code><\/pre>\n\n\n\nPlease 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!<\/p>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[8,35,13],"tags":[],"class_list":["post-5019","post","type-post","status-publish","format-standard","hentry","category-how-to","category-netsuite","category-software"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=\/wp\/v2\/posts\/5019","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5019"}],"version-history":[{"count":1,"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=\/wp\/v2\/posts\/5019\/revisions"}],"predecessor-version":[{"id":5020,"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=\/wp\/v2\/posts\/5019\/revisions\/5020"}],"wp:attachment":[{"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scottdanesi.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}