InventSum

-- ============================================================
-- Table  : InventSum Aggregated on-hand quantities by item and inventory dimension — physical, available, and posted balances
-- Purpose: Column-selected read of InventSum — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + ItemId + InventDimId
-- Notes  : Auto-generated skeleton for Synapse Link / Fabric Link-landed F&O data (lowercase column names). Enums decoded inline where verified; datetimes stored in UTC; 1900-01-01 dates are sentinels mapped to NULL. System/audit columns omitted — see the quirks guide.
-- ============================================================
SELECT
  s.itemid AS "Item the on-hand balance is for",
  s.inventdimid AS "Inventory-dimension combination the balance is aggregated at; join to InventDim to resolve site / warehouse / location",
  s.physicalinvent AS "Physically on-hand quantity for the item and dimension",
  s.availphysical AS "Physically available quantity (on-hand less physical reservations)",
  s.postedqty AS "Financially posted quantity for the item and dimension",
  s.closed AS "Whether the on-hand row is closed (zero balance) and excluded from active on-hand",  -- enum: decode s.closed via GlobalOptionsetMetadata join — see quirks guide #enums
  s.physicalvalue AS "Value of the physically posted quantity for the item and dimension",
  s.postedvalue AS "Financially posted value for the item and dimension",
  s.availordered AS "Total available quantity including ordered receipts, less ordered reservations",
  s.ordered AS "Quantity on ordered receipts (purchase orders and other inbound supply)",
  s.onorder AS "Quantity on open issue demand (sales orders and other outbound demand)",
  s.arrived AS "Quantity arrived but not yet received into on-hand",
  s.received AS "Quantity physically received (product receipt posted, not yet invoiced)",
  s.registered AS "Quantity registered at receipt but not yet posted",
  s.picked AS "Quantity picked for outbound orders but not yet deducted",
  s.deducted AS "Quantity deducted (packing slip posted, not yet invoiced)",
  s.reservphysical AS "Quantity reserved against physical on-hand",
  s.reservordered AS "Quantity reserved against ordered (not yet received) supply",
  s.quotationissue AS "Quantity tied up on open sales quotations",
  s.quotationreceipt AS "Quantity expected from open purchase quotations (RFQs)",
  s.closedqty AS "Whether the quantity side of the record is fully settled by inventory closing",  -- enum: decode s.closedqty via GlobalOptionsetMetadata join — see quirks guide #enums
  s.isexcludedfrominventoryvalue AS "Whether the record is excluded from inventory value reporting",  -- enum: decode s.isexcludedfrominventoryvalue via GlobalOptionsetMetadata join — see quirks guide #enums
  CASE WHEN CAST(s.lastupddateexpected AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(s.lastupddateexpected AS DATE) END AS "Date the expected (ordered/on-order) buckets last changed; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(s.lastupddatephysical AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(s.lastupddatephysical AS DATE) END AS "Date the physical buckets last changed; unset rows carry the 1900-01-01 sentinel"  -- 1900-01-01 sentinel → NULL
FROM <catalog>.<schema>.inventsum s
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = s.inventdimid AND id.dataareaid = s.dataareaid
WHERE
  s.dataareaid = '<company>'
  -- AND s.itemid = '<ItemId>'
ORDER BY s.itemid;