F47022

-- ============================================================
-- Table  : F47022 One row per acknowledged purchase order line on an inbound 855; staging mirror of the PO detail F4311.
-- Purpose: Column-selected read of F47022 — auto-generated from field metadata
-- Grain  : One row per SZEKCO + SZEDOC + SZEDCT + SZEDLN
-- 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.SZEKCO AS "Key company that scopes the EDI document number; part of the staging primary key.",
  f.SZEDOC AS "EDI document number assigned when the transaction is staged; the primary handle for one staged EDI document.",
  f.SZEDCT AS "EDI document type qualifier; pairs with document number and key company to complete the staging key.",
  f.SZEDLN / POWER(10, 3) AS "EDI line number within the document; three implied decimals, so line 1.000 is stored as 1000.",  -- implied decimals: 3 (verify in F9210)
  f.SZEDST AS "X12 transaction-set number this row stages (850, 855, 856, 810).",
  f.SZEDER AS "Direction flag: R for documents received from the trading partner, S for documents JDE is sending.",
  f.SZEDSP AS "Processed flag: Y once the edit/update program has moved this row to or from the live application tables. Filter N for the open queue; Y rows are the reconciliation audit trail.",
  f.SZEDBT AS "Batch number grouping documents staged in the same translator run.",
  f.SZPNID AS "Trading partner identifier agreed with the customer or supplier; the natural grain for partner-level EDI scorecards.",
  f.SZKCOO AS "Company segment of the JDE order number key.",
  f.SZDOCO AS "JDE purchase order number being acknowledged; the join back to F4311.",
  f.SZDCTO AS "JDE order type of the related order (SO, OP, and so on).",
  f.SZSFXO AS "Order suffix; distinguishes multiple documents against the same order number.",
  f.SZLNID / POWER(10, 3) AS "JDE order line number; three implied decimals (line 1.000 stored as 1000).",  -- implied decimals: 3 (verify in F9210)
  TRIM(f.SZMCU) AS "Branch/plant on the order; right-justified 12-character business unit.",
  f.SZAN8 AS "Supplier address book number on the acknowledged purchase order.",
  f.SZSHAN AS "Ship-to address book number.",
  CASE WHEN f.SZDRQJ IS NULL OR f.SZDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SZDRQJ AS INT) DIV 1000, 1, 1), CAST(f.SZDRQJ AS INT) % 1000 - 1) END AS "Requested date on the order line or document (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.SZTRDJ IS NULL OR f.SZTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SZTRDJ AS INT) DIV 1000, 1, 1), CAST(f.SZTRDJ AS INT) % 1000 - 1) END AS "Order or transaction date (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.SZPPDJ IS NULL OR f.SZPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SZPPDJ AS INT) DIV 1000, 1, 1), CAST(f.SZPPDJ AS INT) % 1000 - 1) END AS "Supplier-promised shipment date (Julian).",  -- CYYDDD Julian → DATE
  f.SZITM AS "Short (internal numeric) item number.",
  f.SZLITM AS "Second item number, the human-readable part number analysts usually report on.",
  f.SZCITM AS "The trading partner's own item number as sent on the EDI document; key for cross-reference quality checks.",
  f.SZLNTY AS "Line type controlling how the line hits inventory and the ledger (stock, non-stock, freight).",
  f.SZUOM AS "Unit of measure the quantity was transacted in.",
  f.SZUORG AS "Ordered or transaction quantity for the line.",
  f.SZUOPN AS "Quantity still open on the line.",
  f.SZPRRC / POWER(10, 4) AS "Unit cost confirmed by the supplier; four implied decimals.",  -- implied decimals: 4 (verify in F9210)
  f.SZECST / POWER(10, 2) AS "Extended cost for the line; pair with extended price for staged margin checks.",  -- implied decimals: 2 (verify in F9210)
  f.SZSTTS AS "Line status code on the staged line.",
  f.SZCRCD AS "Transaction currency code.",
  f.SZLSTS AS "Line item status reported on the acknowledgment (accepted, changed, rejected)."
FROM <catalog>.<schema_data>.f47022 f
WHERE
  f.SZTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.SZTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.SZEKCO;