inventoryitemlocations

-- ============================================================
-- Table  : inventoryitemlocations — The per-item-per-location balance and policy record: on hand, available, committed, back-ordered and in-transit quantities alongside the reorder point, safety stock, lead times and the location's cost basis.
-- Purpose: Column-selected read of inventoryitemlocations — auto-generated from field metadata
-- Grain  : One row per item + location
-- Caution: on-hand VALUE (onhandvaluemli) is a costing output, not a transaction sum — under Average, FIFO, LIFO or Standard costing you cannot reconstruct it by summing transaction amounts; see quirks #costing.
-- Notes  : Auto-generated skeleton for NetSuite data landed in your lakehouse from a SuiteAnalytics Connect (or SuiteQL) extract — it never addresses live NetSuite. Identifiers are lowercase as NetSuite2.com renders them. Select-type columns hold numeric internal ids: BUILTIN.DF() display resolution exists only at extraction time, so decode ids by joining the landed list records; see quirks #display-values. Check-box columns arrive as 'T'/'F' strings; see quirks #tf-booleans.
-- ============================================================
SELECT
  t.item AS "Item (internal id)",
  t.location AS "Location (internal id)",
  t.quantityonhand AS "Quantity on hand at this location",
  t.quantityavailable AS "Quantity available",
  t.quantitycommitted AS "Quantity committed to orders",
  t.quantitybackordered AS "Quantity back ordered",
  t.quantityintransit AS "Quantity in transit",
  t.quantityonorder AS "Quantity on order",
  t.reorderpoint AS "Reorder point",
  t.preferredstocklevel AS "Preferred stock level",
  t.safetystocklevel AS "Safety stock level",
  t.onhandvaluemli AS "On-hand value — a costing output, not a transaction sum",
  t.averagecostmli AS "Average cost at this location",
  t.lastpurchasepricemli AS "Last purchase price at this location",
  t.cost AS "Standard cost",
  t.leadtime AS "Replenishment lead time in days",
  t.lastinvtcountdate AS "Last cycle-count date",
  t.nextinvtcountdate AS "Next scheduled count date"
FROM <catalog>.<schema>.inventoryitemlocations t
WHERE
  t.location = <location_id>  -- location scope — see quirks guide #oneworld
  -- AND t.item = <item>
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
ORDER BY t.item;