VendInvoiceJour

-- ============================================================
-- Table  : VendInvoiceJour Posted vendor invoice header — one row per posted AP invoice, carrying totals, due date, cash-discount terms, and the ledger voucher
-- Purpose: Column-selected read of VendInvoiceJour — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + InvoiceAccount + InvoiceId + InvoiceDate
-- 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
  v.invoiceaccount AS "Vendor account the invoice is settled against — part of the natural key",
  v.invoiceid AS "The vendor's invoice number — unique only together with the account and invoice date; 20 chars by default, 50 where the 10.0.40+ extended vendor invoice number feature is enabled",
  v.invoicedate AS "Invoice date — the primary analysis date, and part of the natural key",
  v.orderaccount AS "Vendor account the goods were ordered from, when it differs from the invoice account",
  v.purchid AS "Purchase order the invoice was posted against; empty for non-PO invoices",
  v.internalinvoiceid AS "Internal invoice number assigned by the company's number sequence",
  v.ledgervoucher AS "Ledger voucher the invoice posted under",
  v.invoiceamount AS "Invoice total in the invoice currency",
  v.invoiceamountmst AS "Invoice total restated in the accounting (company) currency",
  v.salesbalance AS "Net goods value of the invoice lines, before tax and charges",
  v.sumtax AS "Total sales tax on the invoice",
  v.cashdisc AS "Cash discount amount offered on the invoice",
  CASE WHEN CAST(v.cashdiscdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.cashdiscdate AS DATE) END AS "Last date the cash discount can be taken; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(v.duedate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.duedate AS DATE) END AS "Payment due date per the invoice's terms; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(v.documentdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(v.documentdate AS DATE) END AS "The vendor's document date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  v.paymid AS "Payment identifier used to match the settling payment",
  v.postingprofile AS "Vendor posting profile the invoice posted with",
  v.currencycode AS "Invoice currency",
  v.qty AS "Total quantity across the invoice lines"
FROM <catalog>.<schema>.vendinvoicejour v
WHERE
  v.dataareaid = '<company>'
  -- AND v.invoiceaccount = '<VendAccount>'
  -- AND v.invoicedate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND v.invoicedate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY v.invoiceaccount;