InventJournalTrans

-- ============================================================
-- Table  : InventJournalTrans Inventory journal line — one row per item movement in a journal, with quantity, cost, counted figures, and the dimension combination it posts against
-- Purpose: Column-selected read of InventJournalTrans — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + JournalId + LineNum
-- 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
  i.journalid AS "The journal this line belongs to",
  i.linenum AS "Line number within the journal",
  i.transdate AS "Posting date of the line — the primary analysis date (a plain date, not a datetime)",
  i.itemid AS "Item the line moves, adjusts, or counts",
  i.inventdimid AS "Inventory-dimension combination the line posts against; join to InventDim to resolve site / warehouse / batch",
  i.journaltype AS "Journal type copied down from the header, so lines can be filtered without a join",  -- enum: decode i.journaltype via GlobalOptionsetMetadata join — see quirks guide #enums
  i.qty AS "Quantity posted by the line, signed (negative for issues)",
  i.costamount AS "Cost amount of the line in company currency",
  i.costprice AS "Unit cost price used for the line",
  i.priceunit AS "Number of units the cost price applies to (price per 1, 100, 1000…)",
  i.costmarkup AS "Charge amount added on top of the cost price",
  i.salesamount AS "Sales amount for the line, where the journal type carries one",
  i.inventonhand AS "Expected on-hand quantity at counting time — compare with Counted for count variance",
  i.counted AS "Quantity actually counted on a counting journal line",
  i.countingreasoncode AS "Reason code recorded for the count difference",
  i.voucher AS "Ledger voucher the line posted under",
  i.inventtransid AS "Transaction reference linking the line to its InventTransOrigin and inventory transactions",
  i.toinventdimid AS "Destination dimension combination on transfer journal lines (from-side lives in InventDimId)",
  i.toinventtransid AS "Transaction reference of the destination side of a transfer line",
  i.defaultdimension AS "RecId of the line's financial-dimension value set in DimensionAttributeValueSet",
  CASE WHEN CAST(i.releasedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(i.releasedate AS DATE) END AS "Date a blocked line is released for posting; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  i.unit AS "Unit of measure the line quantity is entered in"
FROM <catalog>.<schema>.inventjournaltrans i
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = i.inventdimid AND id.dataareaid = i.dataareaid
-- Resolve DimensionAttributeValueSet (via DefaultDimension): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.dimensionattributevalueset dim ON dim.recid = i.defaultdimension
WHERE
  i.dataareaid = '<company>'
  -- AND i.transdate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND i.transdate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY i.journalid;