SalesLine

-- ============================================================
-- Table  : SalesLine Sales order line — one row per order line; the core sell-side transaction, joined to its header by SalesId
-- Purpose: Column-selected read of SalesLine — auto-generated from field metadata
-- Grain  : One row per company (dataareaid) + SalesId + LineNum
-- 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
  sl.salesid AS "The sales order this line belongs to",
  sl.linenum AS "Line number within the order",
  sl.itemid AS "Item ordered on the line",
  sl.inventdimid AS "Inventory-dimension combination the line ships from; join to InventDim to resolve site / warehouse",
  CASE sl.salesstatus WHEN 0 THEN 'None' WHEN 1 THEN 'Backorder' WHEN 2 THEN 'Delivered' WHEN 3 THEN 'Invoiced' WHEN 4 THEN 'Canceled' ELSE CAST(sl.salesstatus AS STRING) END AS "Line status across its life cycle",  -- enum: SalesStatus
  sl.salesqty AS "Quantity ordered on the line in the sales unit",
  sl.lineamount AS "Net line amount in the order currency",
  sl.name AS "Line description",
  CASE WHEN CAST(sl.confirmeddlv AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(sl.confirmeddlv AS DATE) END AS "Confirmed delivery date; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  sl.salesunit AS "Sales unit of measure for the ordered quantity",
  sl.salesprice AS "Unit sales price in the order currency",
  sl.currencycode AS "Transaction currency of the line (matches the header)",
  sl.linedisc AS "Line discount amount per price unit",
  sl.linepercent AS "Line discount percentage",
  sl.qtyordered AS "Quantity ordered in the inventory unit",
  sl.remainsalesphysical AS "Open (not yet delivered) quantity in the sales unit — the backlog measure",
  sl.remaininventphysical AS "Open (not yet delivered) quantity in the inventory unit",
  sl.shippingdaterequested AS "Requested ship date for the line — the line's primary analysis date",
  CASE WHEN CAST(sl.shippingdateconfirmed AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(sl.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(sl.receiptdaterequested AS DATE) = DATE '1900-01-01' THEN NULL ELSE CAST(sl.receiptdaterequested AS DATE) END AS "Requested receipt date for the line; unset rows carry the 1900-01-01 sentinel",  -- 1900-01-01 sentinel → NULL
  sl.custaccount AS "Customer account on the line (copied from the header)",
  sl.custgroup AS "Customer group on the line",
  sl.taxgroup AS "Sales tax group applied to the line",
  sl.taxitemgroup AS "Item sales tax group applied to the line",
  sl.inventtransid AS "Inventory transaction reference linking the line to its stock movements via InventTransOrigin",
  sl.salestype AS "Type of the order the line belongs to (journal, subscription, sales order, returned order…)",  -- enum: decode sl.salestype via GlobalOptionsetMetadata join — see quirks guide #enums
  sl.itembomid AS "BOM version used when the line explodes into components",
  sl.projid AS "Project the line is attached to, for project-invoiced sales",
  sl.blocked AS "Whether further processing of the line is blocked",  -- enum: decode sl.blocked via GlobalOptionsetMetadata join — see quirks guide #enums
  sl.complete AS "Whether the line must be delivered complete (no partial deliveries)",  -- enum: decode sl.complete via GlobalOptionsetMetadata join — see quirks guide #enums
  sl.deliverypostaladdress AS "RecId of the line-level ship-to address in LogisticsPostalAddress, when it differs from the header"
FROM <catalog>.<schema>.salesline sl
-- Dimension expansion: uncomment to resolve site / warehouse / location / batch
-- LEFT JOIN <catalog>.<schema>.inventdim id ON id.inventdimid = sl.inventdimid AND id.dataareaid = sl.dataareaid
-- Resolve LogisticsPostalAddress (via DeliveryPostalAddress): uncomment to join on its RecId surrogate key
-- LEFT JOIN <catalog>.<schema>.logisticspostaladdress log ON log.recid = sl.deliverypostaladdress
WHERE
  sl.dataareaid = '<company>'
  -- AND sl.salesid = '<SalesId>'
  -- AND sl.shippingdaterequested >= '<DATE_FROM>'  -- yyyy-MM-dd (UTC)
  -- AND sl.shippingdaterequested <= '<DATE_TO>'  -- yyyy-MM-dd (UTC)
ORDER BY sl.salesid;