This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Anyone know how to get the current $ value of inventory for one item at specific location? I know it can be done by totaling up all inventory affecting transactions, but am hoping its also available in a table somewhere

```-- Joseph Lee
-- Main Warehouse On Hand Inventory Analysis
-- Bin, Quantities, Costs &amp; Total Inventory On Hand Cost Sum
SELECT
  *
FROM
  (
    SELECT
      NULL AS "Item ID",
      NULL AS Item_name,
      NULL AS "Bin id",
      NULL AS "Bin name",
      NULL AS "Bin quantity",
      NULL AS "Item average cost",
      TO_CHAR(ROUND(SUM(item.averageCost*Onhand), 2), '$999,999,999.00') AS "Item Average Cost Per Bin",
      NULL AS "warehouse zone",
      NULL AS "location",	
    FROM
      ItemBinQuantity
    LEFT OUTER JOIN bin ON itemBinQuantity.bin = bin.id
    LEFT OUTER JOIN item ON itemBinQuantity.item = item.id
    WHERE
      ( item.custitem_mm_saleable_item = 'T' )
      AND ( OnHand &gt; 0 ) 
      AND ( bin.type IN ( 'STORAGE','PICKING' ) AND ( BUILTIN.DF( bin.location ) = 'Main' ) )
    UNION ALL
    SELECT
      Item AS "Item ID",
      BUILTIN.DF( Item ) AS Item_Name,
      Bin AS "Bin ID",
      BUILTIN.DF( Bin ) AS "Bin Name",
      OnHand AS "Bin Quantity",
      TO_CHAR(item.averageCost, '$999,999,999.00') AS "Item Average cost",
      TO_CHAR(item.averageCost*Onhand, '$999,999,999.00') AS "Item Average Cost Per Bin",
      BUILTIN.DF( bin.custrecord_wmsse_zone ) AS "Warehouse Zone",
      BUILTIN.DF( bin.location ) AS "Location"
    FROM
      ItemBinQuantity
    LEFT OUTER JOIN bin ON itemBinQuantity.bin = bin.id
    LEFT OUTER JOIN item on itemBinQuantity.item = item.id
    WHERE
      ( item.custitem_mm_saleable_item = 'T' )
      AND ( OnHand &gt; 0 ) 
      AND ( bin.type IN ( 'STORAGE','PICKING' ) AND ( BUILTIN.DF( bin.location ) = 'Main' ) )
  )
ORDER BY
  item_name```

You'll have to edit this for your needs. But its just looking at inventory per bin and multiply by average cost. Then i union to get a bottom total amount row. Should get you in the ball park.