PurchLine

-- ============================================================
-- Table  : PurchLine Purchase order line — one row per order line; the core buy-side transaction, joined to its header by PurchId
-- Purpose: Column-selected read of PurchLine — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + PurchId + LineNumber
-- 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
  pl.purchid AS "The purchase order this line belongs to",
  pl.linenumber AS "Line number within the order — a 64-bit sequence, unlike the decimal LineNum on sales lines",
  pl.itemid AS "Item ordered on the line",
  pl.inventdimid AS "Inventory-dimension combination the line receives into; join to InventDim to resolve site / warehouse",
  pl.name AS "Line description",
  pl.purchqty AS "Quantity ordered in the purchase unit",
  pl.qtyordered AS "Quantity ordered restated in the inventory unit",
  pl.purchunit AS "Purchase unit of measure",
  pl.purchprice AS "Unit price in the order currency",
  pl.priceunit AS "Number of units the price applies to",
  pl.linedisc AS "Line discount amount per price unit",
  pl.lineamount AS "Net line amount in the order currency",
  pl.currencycode AS "Order currency the line prices in",
  pl.purchstatus AS "Line status — how far the line has progressed through receipt and invoicing",  -- enum: decode pl.purchstatus via GlobalOptionsetMetadata join — see quirks guide #enums
  pl.deliverydate AS "Requested delivery date; the primary analysis date for the line",
  CASE WHEN CAST(pl.confirmeddlv AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(pl.confirmeddlv AS DATE) END AS "Confirmed delivery date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  pl.vendaccount AS "Vendor account, defaulted from the order header",
  pl.remainpurchphysical AS "Quantity still to be received (the deliver remainder), in the purchase unit",
  pl.remainpurchfinancial AS "Quantity still to be invoiced (the invoice remainder)",
  pl.purchreceivednow AS "Quantity staged to receive on the next receipt posting",
  pl.defaultdimension AS "RecId of the line's default financial-dimension value set in DimensionAttributeValueSet",
  pl.projid AS "Project the line is attached to"
FROM <catalog>.<schema>.purchline pl
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = pl.inventdimid AND id.dataareaid = pl.dataareaid
WHERE
  pl.dataareaid = '<company>'
  -- AND pl.purchid = '<PurchId>'
  -- AND pl.deliverydate >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND pl.deliverydate <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY pl.purchid;