F4211

-- ============================================================
-- Table  : F4211 Sales order detail — one row per order line; the core sales transaction table
-- Purpose: Column-selected read of F4211 — auto-generated from field metadata
-- Grain  : One row per SDKCOO + SDDOCO + SDDCTO + SDLNID
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  d.SDKCOO AS "Order key company - disambiguates order numbers across companies",
  d.SDDOCO AS "Order number",
  d.SDDCTO AS "Order type code (SO, ST, CO, ...)",
  d.SDLNID / POWER(10, 3) AS "Order line number, stored x1000 (3 implied decimals)",  -- implied decimals: 3 (verify in F9210)
  d.SDSFXO AS "Order suffix, used to split partial shipments/invoices",
  TRIM(d.SDMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  d.SDCO AS "Company the transaction belongs to",
  TRIM(d.SDEMCU) AS "Header branch plant of the order (right-justified, space-padded)",
  d.SDOORN AS "Original order number when this order was generated from another",
  d.SDOCTO AS "Original order type",
  d.SDRORN AS "Related order number (e.g. transfer or work order)",
  d.SDAN8 AS "Sold-to customer address book number",
  d.SDSHAN AS "Ship-to address book number",
  d.SDITM AS "Short item number - the internal numeric item key",
  d.SDLITM AS "Second item number - the primary user-facing item code",
  d.SDAITM AS "Third item number - catalog or cross-reference item code",
  d.SDLOCN AS "Storage location within the branch plant",
  d.SDLOTN AS "Lot or serial number",
  d.SDDSC1 AS "Description line 1",
  d.SDLNTY AS "Line type controlling inventory/GL/AR interfaces (S, N, F, ...)",
  d.SDLTTR AS "Last status the line has reached in the order activity flow",
  d.SDNXTR AS "Next expected status in the order activity flow",
  CASE WHEN d.SDTRDJ IS NULL OR d.SDTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDTRDJ AS INT) DIV 1000, 1, 1), CAST(d.SDTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDDRQJ IS NULL OR d.SDDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDDRQJ AS INT) DIV 1000, 1, 1), CAST(d.SDDRQJ AS INT) % 1000 - 1) END AS "Date the customer requested the goods",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDPDDJ IS NULL OR d.SDPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDPDDJ AS INT) DIV 1000, 1, 1), CAST(d.SDPDDJ AS INT) % 1000 - 1) END AS "Scheduled pick date",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDOPDJ IS NULL OR d.SDOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDOPDJ AS INT) DIV 1000, 1, 1), CAST(d.SDOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date, kept for on-time measurement",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDPPDJ IS NULL OR d.SDPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDPPDJ AS INT) DIV 1000, 1, 1), CAST(d.SDPPDJ AS INT) % 1000 - 1) END AS "Currently promised ship date",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDADDJ IS NULL OR d.SDADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDADDJ AS INT) DIV 1000, 1, 1), CAST(d.SDADDJ AS INT) % 1000 - 1) END AS "Actual ship date",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDIVD IS NULL OR d.SDIVD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDIVD AS INT) DIV 1000, 1, 1), CAST(d.SDIVD AS INT) % 1000 - 1) END AS "Invoice date",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDCNDJ IS NULL OR d.SDCNDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDCNDJ AS INT) DIV 1000, 1, 1), CAST(d.SDCNDJ AS INT) % 1000 - 1) END AS "Date the order or line was canceled",  -- CYYDDD Julian → DATE
  CASE WHEN d.SDDGL IS NULL OR d.SDDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(d.SDDGL AS INT) DIV 1000, 1, 1), CAST(d.SDDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  d.SDUOM AS "Unit of measure the transaction was entered in",
  d.SDUORG AS "Quantity ordered, in transaction UOM",
  d.SDSOQS AS "Quantity shipped",
  d.SDSOBK AS "Quantity backordered or on hold",
  d.SDSOCN AS "Quantity canceled",
  d.SDAOPN / POWER(10, 2) AS "Open amount not yet shipped/invoiced",  -- implied decimals: 2 (verify in F9210)
  d.SDUPRC / POWER(10, 4) AS "Unit price in domestic currency (4 implied decimals)",  -- implied decimals: 4 (verify in F9210)
  d.SDAEXP / POWER(10, 2) AS "Extended price in domestic currency",  -- implied decimals: 2 (verify in F9210)
  d.SDUNCS / POWER(10, 4) AS "Unit cost (4 implied decimals)"  -- implied decimals: 4 (verify in F9210)
  -- … plus 7 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f4211 d
WHERE
  d.SDKCOO = '<KCOO>'
  -- AND d.SDDOCO = <DOCO>
  -- AND d.SDDCTO = '<DCTO>'
  -- AND d.SDTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND d.SDTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY d.SDKCOO;