F42119

-- ============================================================
-- Table  : F42119 Sales order detail history — eligible lines copied from F4211 by configured sales-update/history purging; same layout as F4211
-- Purpose: Column-selected read of F42119 — 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
  dh.SDKCOO AS "Order key company - disambiguates order numbers across companies",
  dh.SDDOCO AS "Order number",
  dh.SDDCTO AS "Order type code (SO, ST, CO, ...)",
  dh.SDLNID / POWER(10, 3) AS "Order line number, stored x1000 (3 implied decimals)",  -- implied decimals: 3 (verify in F9210)
  dh.SDSFXO AS "Order suffix, used to split partial shipments/invoices",
  TRIM(dh.SDMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  dh.SDCO AS "Company the transaction belongs to",
  TRIM(dh.SDEMCU) AS "Header branch plant of the order (right-justified, space-padded)",
  dh.SDOORN AS "Original order number when this order was generated from another",
  dh.SDOCTO AS "Original order type",
  dh.SDRORN AS "Related order number (e.g. transfer or work order)",
  dh.SDAN8 AS "Sold-to customer address book number",
  dh.SDSHAN AS "Ship-to address book number",
  dh.SDITM AS "Short item number - the internal numeric item key",
  dh.SDLITM AS "Second item number - the primary user-facing item code",
  dh.SDAITM AS "Third item number - catalog or cross-reference item code",
  dh.SDLOCN AS "Storage location within the branch plant",
  dh.SDLOTN AS "Lot or serial number",
  dh.SDDSC1 AS "Description line 1",
  dh.SDLNTY AS "Line type controlling inventory/GL/AR interfaces (S, N, F, ...)",
  dh.SDLTTR AS "Last status the line has reached in the order activity flow",
  dh.SDNXTR AS "Next expected status in the order activity flow",
  CASE WHEN dh.SDTRDJ IS NULL OR dh.SDTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDTRDJ AS INT) DIV 1000, 1, 1), CAST(dh.SDTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDDRQJ IS NULL OR dh.SDDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDDRQJ AS INT) DIV 1000, 1, 1), CAST(dh.SDDRQJ AS INT) % 1000 - 1) END AS "Date the customer requested the goods",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDPDDJ IS NULL OR dh.SDPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDPDDJ AS INT) DIV 1000, 1, 1), CAST(dh.SDPDDJ AS INT) % 1000 - 1) END AS "Scheduled pick date",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDOPDJ IS NULL OR dh.SDOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDOPDJ AS INT) DIV 1000, 1, 1), CAST(dh.SDOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date, kept for on-time measurement",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDPPDJ IS NULL OR dh.SDPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDPPDJ AS INT) DIV 1000, 1, 1), CAST(dh.SDPPDJ AS INT) % 1000 - 1) END AS "Currently promised ship date",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDADDJ IS NULL OR dh.SDADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDADDJ AS INT) DIV 1000, 1, 1), CAST(dh.SDADDJ AS INT) % 1000 - 1) END AS "Actual ship date",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDIVD IS NULL OR dh.SDIVD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDIVD AS INT) DIV 1000, 1, 1), CAST(dh.SDIVD AS INT) % 1000 - 1) END AS "Invoice date",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDCNDJ IS NULL OR dh.SDCNDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDCNDJ AS INT) DIV 1000, 1, 1), CAST(dh.SDCNDJ AS INT) % 1000 - 1) END AS "Date the order or line was canceled",  -- CYYDDD Julian → DATE
  CASE WHEN dh.SDDGL IS NULL OR dh.SDDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(dh.SDDGL AS INT) DIV 1000, 1, 1), CAST(dh.SDDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  dh.SDUOM AS "Unit of measure the transaction was entered in",
  dh.SDUORG AS "Quantity ordered, in transaction UOM",
  dh.SDSOQS AS "Quantity shipped",
  dh.SDSOBK AS "Quantity backordered or on hold",
  dh.SDSOCN AS "Quantity canceled",
  dh.SDAOPN / POWER(10, 2) AS "Open amount not yet shipped/invoiced",  -- implied decimals: 2 (verify in F9210)
  dh.SDUPRC / POWER(10, 4) AS "Unit price in domestic currency (4 implied decimals)",  -- implied decimals: 4 (verify in F9210)
  dh.SDAEXP / POWER(10, 2) AS "Extended price in domestic currency",  -- implied decimals: 2 (verify in F9210)
  dh.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>.f42119 dh
WHERE
  dh.SDKCOO = '<KCOO>'
  -- AND dh.SDDOCO = <DOCO>
  -- AND dh.SDDCTO = '<DCTO>'
  -- AND dh.SDTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND dh.SDTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY dh.SDKCOO;