F42199

-- ============================================================
-- Table  : F42199 Sales order detail ledger - writes a history row every time an F4211 line changes, so each line can appear many times keyed by update date/time
-- Purpose: Column-selected read of F42199 — auto-generated from field metadata
-- Grain  : One row per SLKCOO + SLDOCO + SLDCTO + SLLNID + SLUPMJ + SLTDAY
-- 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
  f.SLKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  f.SLDOCO AS "Order number",
  f.SLDCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  f.SLLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  CASE WHEN f.SLUPMJ IS NULL OR f.SLUPMJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLUPMJ AS INT) DIV 1000, 1, 1), CAST(f.SLUPMJ AS INT) % 1000 - 1) END AS "Date this ledger snapshot was written; part of the primary key",  -- CYYDDD Julian → DATE
  f.SLTDAY AS "Time this ledger snapshot was written; part of the primary key",
  f.SLSFXO AS "Order suffix distinguishing multiple documents under one order number",
  TRIM(f.SLMCU) AS "Detail branch/plant the line ships from; right-justified, space-padded to 12",
  f.SLCO AS "Company code",
  TRIM(f.SLEMCU) AS "Header (originating) business unit; right-justified, space-padded to 12",
  f.SLOORN AS "Order number of the originating document this record came from",
  f.SLOCTO AS "Document type of the originating order",
  f.SLRORN AS "Cross-referenced related order number (PO, SO, or work order)",
  f.SLAN8 AS "Sold-to customer address book number",
  f.SLSHAN AS "Ship-to address book number",
  f.SLITM AS "Short internal item number - joins to F4101/F4102",
  f.SLLITM AS "Second item number, the long code users normally key",
  f.SLAITM AS "Third item number (catalog or alternate identifier)",
  f.SLLOCN AS "Warehouse storage location",
  f.SLLOTN AS "Lot or serial number",
  f.SLDSC1 AS "Line description",
  f.SLLNTY AS "Line type controlling G/L, inventory, and A/P-A/R interfaces",
  f.SLLTTR AS "Last completed status in the order activity flow",
  f.SLNXTR AS "Next expected status in the order activity flow",
  CASE WHEN f.SLTRDJ IS NULL OR f.SLTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLTRDJ AS INT) DIV 1000, 1, 1), CAST(f.SLTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLDRQJ IS NULL OR f.SLDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLDRQJ AS INT) DIV 1000, 1, 1), CAST(f.SLDRQJ AS INT) % 1000 - 1) END AS "Requested date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLPDDJ IS NULL OR f.SLPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLPDDJ AS INT) DIV 1000, 1, 1), CAST(f.SLPDDJ AS INT) % 1000 - 1) END AS "Scheduled/promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLOPDJ IS NULL OR f.SLOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLOPDJ AS INT) DIV 1000, 1, 1), CAST(f.SLOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLPPDJ IS NULL OR f.SLPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLPPDJ AS INT) DIV 1000, 1, 1), CAST(f.SLPPDJ AS INT) % 1000 - 1) END AS "Promised ship date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLADDJ IS NULL OR f.SLADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLADDJ AS INT) DIV 1000, 1, 1), CAST(f.SLADDJ AS INT) % 1000 - 1) END AS "Actual ship or delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLIVD IS NULL OR f.SLIVD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLIVD AS INT) DIV 1000, 1, 1), CAST(f.SLIVD AS INT) % 1000 - 1) END AS "Invoice date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLCNDJ IS NULL OR f.SLCNDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLCNDJ AS INT) DIV 1000, 1, 1), CAST(f.SLCNDJ AS INT) % 1000 - 1) END AS "Cancel date",  -- CYYDDD Julian → DATE
  CASE WHEN f.SLDGL IS NULL OR f.SLDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SLDGL AS INT) DIV 1000, 1, 1), CAST(f.SLDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  f.SLUOM AS "Transaction unit of measure",
  f.SLUORG AS "Ordered quantity in the transaction unit of measure",
  f.SLSOQS AS "Quantity shipped",
  f.SLSOBK AS "Quantity backordered or held",
  f.SLSOCN AS "Quantity canceled",
  f.SLAOPN / POWER(10, 2) AS "Open amount remaining on the line",  -- implied decimals: 2 (verify in F9210)
  f.SLUPRC / POWER(10, 4) AS "Unit price in domestic currency (4 implied decimals)"  -- implied decimals: 4 (verify in F9210)
  -- … plus 9 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f42199 f
WHERE
  f.SLKCOO = '<KCOO>'
  -- AND f.SLDOCO = <DOCO>
  -- AND f.SLDCTO = '<DCTO>'
  -- AND f.SLTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.SLTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.SLKCOO;