F43199

-- ============================================================
-- Table  : F43199 P.O. detail ledger - audit history of purchase order line changes, including change-order tracking, keyed by an internal unique ID
-- Purpose: Column-selected read of F43199 — auto-generated from field metadata
-- Grain  : One row per OLUKID
-- 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.OLUKID AS "Internal unique key - sole primary key of the ledger",
  f.OLKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  f.OLDOCO AS "Order number",
  f.OLDCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  f.OLSFXO AS "Order suffix distinguishing multiple documents under one order number",
  f.OLLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  TRIM(f.OLMCU) AS "Branch/plant business unit; right-justified, space-padded to 12",
  f.OLCO AS "Company code",
  f.OLOKCO AS "Company key of the originating order",
  f.OLOORN AS "Order number of the originating document this record came from",
  f.OLOCTO AS "Document type of the originating order",
  f.OLRORN AS "Cross-referenced related order number (PO, SO, or work order)",
  f.OLRCTO AS "Document type of the related order",
  f.OLAN8 AS "Supplier address book number",
  f.OLSHAN AS "Ship-to address book number",
  f.OLANBY AS "Buyer address book number",
  f.OLITM AS "Short internal item number - joins to F4101/F4102",
  f.OLLITM AS "Second item number, the long code users normally key",
  f.OLAITM AS "Third item number (catalog or alternate identifier)",
  f.OLLOCN AS "Warehouse storage location",
  f.OLLOTN AS "Lot or serial number",
  f.OLDSC1 AS "Line description",
  f.OLLNTY AS "Line type controlling G/L, inventory, and A/P-A/R interfaces",
  f.OLLTTR AS "Last completed status in the order activity flow",
  f.OLNXTR AS "Next expected status in the order activity flow",
  CASE WHEN f.OLTRDJ IS NULL OR f.OLTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.OLTRDJ AS INT) DIV 1000, 1, 1), CAST(f.OLTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date",  -- CYYDDD Julian → DATE
  CASE WHEN f.OLDRQJ IS NULL OR f.OLDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.OLDRQJ AS INT) DIV 1000, 1, 1), CAST(f.OLDRQJ AS INT) % 1000 - 1) END AS "Requested date",  -- CYYDDD Julian → DATE
  CASE WHEN f.OLPDDJ IS NULL OR f.OLPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.OLPDDJ AS INT) DIV 1000, 1, 1), CAST(f.OLPDDJ AS INT) % 1000 - 1) END AS "Scheduled/promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN f.OLOPDJ IS NULL OR f.OLOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.OLOPDJ AS INT) DIV 1000, 1, 1), CAST(f.OLOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN f.OLADDJ IS NULL OR f.OLADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.OLADDJ AS INT) DIV 1000, 1, 1), CAST(f.OLADDJ AS INT) % 1000 - 1) END AS "Actual date goods shipped or were delivered",  -- CYYDDD Julian → DATE
  CASE WHEN f.OLDGL IS NULL OR f.OLDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.OLDGL AS INT) DIV 1000, 1, 1), CAST(f.OLDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  f.OLUOM AS "Transaction unit of measure",
  f.OLUORG AS "Ordered quantity in the transaction unit of measure",
  f.OLUOPN AS "Open quantity not yet received or shipped",
  f.OLUREC AS "Quantity received to date",
  f.OLUCHG AS "Quantity currently on hold",
  f.OLPRRC / POWER(10, 4) AS "Unit cost in domestic currency (4 implied decimals)",  -- implied decimals: 4 (verify in F9210)
  f.OLAEXP / POWER(10, 2) AS "Extended price of the line in domestic currency",  -- implied decimals: 2 (verify in F9210)
  f.OLAOPN / POWER(10, 2) AS "Open amount remaining on the line",  -- implied decimals: 2 (verify in F9210)
  f.OLAREC / POWER(10, 2) AS "Amount received to date"  -- implied decimals: 2 (verify in F9210)
  -- … plus 10 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f43199 f
WHERE
  f.OLTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.OLTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.OLUKID;