PurchTable

-- ============================================================
-- Table  : PurchTable Purchase order header — one row per order carrying vendor, status, and delivery date
-- Purpose: Column-selected read of PurchTable — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + PurchId
-- 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
  po.purchid AS "The purchase order number — the natural key",
  po.orderaccount AS "Vendor account the order is placed with",
  po.purchstatus AS "Order status across its life cycle",  -- enum: decode po.purchstatus via GlobalOptionsetMetadata join — see quirks guide #enums
  po.purchname AS "Free-text order description",
  po.deliverydate AS "Requested delivery date; the primary analysis date for the header",
  po.invoiceaccount AS "Vendor account the invoice is settled against when it differs from the ordering account",
  po.documentstate AS "Approval state of the order under change management (draft, in review, approved, confirmed)",  -- enum: decode po.documentstate via GlobalOptionsetMetadata join — see quirks guide #enums
  po.documentstatus AS "The furthest document posted for the order — none, purchase order, receipts list, packing slip, or invoice",  -- enum: decode po.documentstatus via GlobalOptionsetMetadata join — see quirks guide #enums
  po.purchasetype AS "Kind of order — purchase, returned order, or journal",  -- enum: decode po.purchasetype via GlobalOptionsetMetadata join — see quirks guide #enums
  CASE WHEN CAST(po.accountingdate AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(po.accountingdate AS DATE) END AS "Date the order posts to the ledger under; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  CASE WHEN CAST(po.confirmeddlv AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(po.confirmeddlv AS DATE) END AS "Confirmed delivery date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  po.currencycode AS "Order currency",
  po.inventsiteid AS "Default receiving site for the order",
  po.inventlocationid AS "Default receiving warehouse for the order",
  po.dlvmode AS "Mode of delivery",
  po.dlvterm AS "Delivery terms",
  po.payment AS "Terms-of-payment code for the order",
  po.paymmode AS "Method of payment",
  po.cashdisc AS "Cash discount code",
  po.cashdiscpercent AS "Cash discount percentage",
  po.discpercent AS "Total discount percentage applied to the order",
  po.purchpoolid AS "Purchase pool the order is grouped under for follow-up",
  po.vendgroup AS "Vendor group, defaulted from the ordering vendor",
  po.defaultdimension AS "RecId of the order's default financial-dimension value set in DimensionAttributeValueSet",
  po.vendorref AS "The vendor's own reference for the order",
  po.requester AS "RecId of the worker who requested the order",
  po.deliveryname AS "Name at the delivery address",
  po.deliverypostaladdress AS "RecId of the delivery postal address in LogisticsPostalAddress",
  po.projid AS "Project the order is attached to",
  po.itembuyergroupid AS "Buyer group responsible for the order"
FROM <catalog>.<schema>.purchtable po
WHERE
  po.dataareaid = '<company>'
  -- AND po.purchid = '<PurchId>'
  -- AND po.deliverydate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND po.deliverydate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY po.purchid;