SalesTable

-- ============================================================
-- Table  : SalesTable Sales order header — one row per order carrying customer, status, site, and requested dates
-- Purpose: Column-selected read of SalesTable — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + 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
  so.salesid AS "The sales order number — the natural key sales lines join back on",
  so.custaccount AS "Customer account the order is for",
  CASE so.salesstatus WHEN 0 THEN 'None' WHEN 1 THEN 'Backorder' WHEN 2 THEN 'Delivered' WHEN 3 THEN 'Invoiced' WHEN 4 THEN 'Canceled' ELSE CAST(so.salesstatus AS STRING) END AS "Order status across its life cycle",  -- enum: SalesStatus
  so.salesname AS "Free-text order description",
  so.inventsiteid AS "Default site for the order",
  so.receiptdaterequested AS "Requested receipt date; the primary analysis date for the header",
  so.invoiceaccount AS "Customer account the order will be invoiced to (can differ from the ordering account)",
  so.custgroup AS "Customer group of the ordering customer, copied to the order",
  so.salestype AS "Type of order (journal, subscription, sales order, returned order…)",  -- enum: decode so.salestype via GlobalOptionsetMetadata join — see quirks guide #enums
  so.currencycode AS "Transaction currency of the order",
  CASE WHEN CAST(so.deliverydate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(so.deliverydate AS DATE) END AS "Delivery date on the order header; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(so.shippingdaterequested AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(so.shippingdaterequested AS DATE) END AS "Requested ship date for the order; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(so.shippingdateconfirmed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(so.shippingdateconfirmed AS DATE) END AS "Confirmed ship date; unset until confirmation, so rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(so.receiptdateconfirmed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(so.receiptdateconfirmed AS DATE) END AS "Confirmed receipt date; unset until confirmation, so rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  so.inventlocationid AS "Default warehouse for the order",
  so.dlvmode AS "Mode of delivery for the order",
  so.dlvterm AS "Terms of delivery for the order",
  so.payment AS "Terms of payment for the order (note: the column is Payment, not PaymTermId)",
  so.taxgroup AS "Sales tax group for the order",
  so.customerref AS "The customer's own reference for the order",
  so.purchorderformnum AS "The customer's purchase order number",
  so.salesoriginid AS "Sales origin (channel) code recording where the order came from",
  so.salespoolid AS "Sales pool used to group orders for processing and reporting",
  so.salesgroup AS "Commission sales group on the order",
  so.workersalestaker AS "RecId of the worker who took the order (references the HR worker table)",
  so.workersalesresponsible AS "RecId of the worker responsible for the order (references the HR worker table)",
  so.languageid AS "Language documents for the order are printed in",
  so.deliverypostaladdress AS "RecId of the order's ship-to address in LogisticsPostalAddress"
FROM <catalog>.<schema>.salestable so
-- Resolve LogisticsPostalAddress (via DeliveryPostalAddress): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.logisticspostaladdress log ON log.recid = so.deliverypostaladdress
WHERE
  so.dataareaid = '<company>'
  -- AND so.salesid = '<SalesId>'
  -- AND so.receiptdaterequested >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND so.receiptdaterequested <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY so.salesid;