CustInvoiceJour

-- ============================================================
-- Table  : CustInvoiceJour Posted customer invoice header — one row per invoice issued from a sales order (or free-text posting), with totals, currency, due date, and the ledger voucher it posted under
-- Purpose: Column-selected read of CustInvoiceJour — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + InvoiceId + InvoiceDate + numberSequenceGroup + SalesId
-- 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 "The invoice number; unique only together with InvoiceDate, numberSequenceGroup, and SalesId",
  c.invoicedate AS "Date the invoice was posted — the primary analysis date for revenue reporting",
  c.numbersequencegroup AS "Number-sequence group the invoice number was drawn from; part of the composite key (often blank)",
  c.salesid AS "Sales order the invoice was posted from; part of the composite key",
  c.invoiceaccount AS "Customer account the invoice is billed to",
  c.orderaccount AS "Customer account that placed the underlying order (can differ from the invoice account)",
  c.custgroup AS "Customer group of the invoiced customer at posting time",
  c.invoiceamount AS "Total invoice amount in the transaction currency",
  c.invoiceamountmst AS "Total invoice amount converted to the company (accounting) currency",
  c.salesbalance AS "Net line amount of the invoice before tax and charges, in the transaction currency",
  c.sumtax AS "Total sales tax on the invoice in the transaction currency",
  c.sumlinedisc AS "Total line discount across the invoice in the transaction currency",
  c.currencycode AS "Transaction currency of the invoice",
  c.exchrate AS "Exchange rate used to convert the invoice to the accounting currency",
  CASE WHEN CAST(c.duedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(c.duedate AS DATE) END AS "Payment due date derived from the invoice date and payment terms; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  c.qty AS "Total quantity invoiced across the lines",
  c.weight AS "Total weight of the invoiced goods",
  c.ledgervoucher AS "Ledger voucher the invoice posted under — the bridge to general-ledger transactions",
  c.salestype AS "Type of the originating sales order (journal, subscription, sales order, returned order…)",  -- enum: decode c.salestype via GlobalOptionsetMetadata join — see quirks guide #enums
  c.dlvmode AS "Mode of delivery on the invoice",
  c.dlvterm AS "Terms of delivery on the invoice",
  c.deliverypostaladdress AS "RecId of the ship-to address in LogisticsPostalAddress",
  c.invoicepostaladdress AS "RecId of the bill-to address in LogisticsPostalAddress"
FROM <catalog>.<schema>.custinvoicejour c
-- Resolve LogisticsPostalAddress (via DeliveryPostalAddress): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.logisticspostaladdress deliverypostaladdress ON deliverypostaladdress.recid = c.deliverypostaladdress
-- Resolve LogisticsPostalAddress (via InvoicePostalAddress): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.logisticspostaladdress invoicepostaladdress ON invoicepostaladdress.recid = c.invoicepostaladdress
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;