CustInvoiceTrans

-- ============================================================
-- Table  : CustInvoiceTrans Posted customer invoice line — one row per invoiced line with item, quantity, price, and amounts; the revenue-by-item grain of order-to-cash analytics
-- Purpose: Column-selected read of CustInvoiceTrans — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + InvoiceId + InvoiceDate + numberSequenceGroup + SalesId + 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
  c.invoiceid AS "Invoice this line belongs to; joins to CustInvoiceJour together with InvoiceDate, numberSequenceGroup, and SalesId",
  c.invoicedate AS "Posting date of the invoice, repeated on the line; part of the composite key and the line-level analysis date",
  c.numbersequencegroup AS "Number-sequence group of the invoice; part of the composite key (often blank)",
  c.salesid AS "Sales order the invoiced line came from; part of the composite key",
  c.linenum AS "Line number within the invoice",
  c.itemid AS "Item invoiced on the line",
  c.inventdimid AS "Inventory-dimension combination the line shipped against; join to InventDim to resolve site / warehouse / batch",
  c.name AS "Line description as printed on the invoice",
  c.qty AS "Quantity invoiced in the sales unit",
  c.salesunit AS "Sales unit of measure for the invoiced quantity",
  c.salesprice AS "Unit sales price in the transaction currency",
  c.lineamount AS "Net line amount in the transaction currency — the revenue measure most analyses sum",
  c.lineamountmst AS "Net line amount converted to the company (accounting) currency",
  c.discamount AS "Line discount amount in the transaction currency",
  c.discpercent AS "Line discount percentage",
  c.currencycode AS "Transaction currency of the line (matches the header)",
  c.inventtransid AS "Inventory transaction reference linking the invoiced line to its stock movements",
  c.origsalesid AS "Original sales order for return/credit lines that reference an earlier order",
  c.taxgroup AS "Sales tax group applied to the line",
  c.taxitemgroup AS "Item sales tax group applied to the line",
  c.externalitemid AS "The customer's own item number for the invoiced item, when maintained"
FROM <catalog>.<schema>.custinvoicetrans c
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = c.inventdimid AND id.dataareaid = c.dataareaid
WHERE
  c.dataareaid = '<company>'
  -- AND c.salesid = '<SalesId>'
  -- AND c.invoicedate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND c.invoicedate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY c.invoiceid;