how to update average unit cost with lots and bins

AI generated answer (using internal Slack threads):

****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ****** To update the average unit cost with lots and bins in NetSuite, you need to ensure that the inventory items are correctly assigned to bins and that the costing method is properly set up. When you turn on "USE BINS" for some items but not all, and the ones you did turn on to use BINS, you didn't do BIN putaway worksheets for 100% of the time, it can cause discrepancies between your inventory subledger and your total inventory GL balances. The GL balance should be derived from the total value at the location level. GL does not consider the quantity in bins for its balance. So if there is a quantity on hand and a cost, it should hit the GL regardless of what bins it is in. If you have 100 units of an item that doesn't use bins, then you turn "USE BINS" on, but don't do a bin putaway entry, the $ amount still lives in the GL balance but the units totally disappear - i.e. it shows we have units in a location, but 0 in any bins. To resolve this, you can use a SQL query to get a full picture of your inventory. Here is a sample query:

SQL
SELECT InventoryNumberInventoryBalance.item
    ,BUILTIN.DF(InventoryNumberInventoryBalance.item) AS itemName
    ,InventoryNumberInventoryBalance.binNumber
    ,BUILTIN.DF(InventoryNumberInventoryBalance.inventoryNumber) AS Lot_number
    ,InventoryNumberInventoryBalance.quantityOnHand
    ,InventoryNumberInventoryBalance.quantityPicked
    ,InventoryNumberInventoryBalance.quantityAvailable
    ,InventoryNumberInventoryBalance.inventoryStatus
    ,InventoryNumberInventoryBalance.committedQtyPerLocation
    ,InventoryNumberInventoryBalance.committedQtyPerSerialLotNumber
    ,InventoryNumberInventoryBalance.committedQtyPerSerialLotNumberLocation
    ,InventoryNumberInventoryBalance.location
FROM InventoryNumberInventoryBalance

This query will give you a detailed view of your inventory, including item names, bin numbers, lot numbers, quantities on hand, picked, and available, inventory status, committed quantities per location, serial lot number, and location. Remember, when you post an Item Receipt with no bins then it always ends up on the bin Putaway worksheet. If you're not seeing any items in the bin Putaway worksheet, try selecting a location and waiting. Sometimes it takes a while for NetSuite to load the data.