F47012

-- ============================================================
-- Table  : F47012 One row per line on an inbound EDI customer purchase order (850); staging mirror of the sales order detail F4211.
-- Purpose: Column-selected read of F47012 — 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 sales order number created or matched when the 850 is processed; the join to F4211.",
  f.SZDCTO AS "JDE order type of the related order (SO, OP, and so on).",
  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.SZCO AS "Company that owns the transaction.",
  f.SZAN8 AS "Primary address book number on the document: customer sold-to on sales documents, supplier on purchasing documents.",
  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 "Promised shipment date (Julian).",  -- CYYDDD Julian → DATE
  f.SZVR01 AS "Free-form customer reference; on customer-facing documents this usually carries the customer's PO number.",
  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.SZLOTN AS "Lot or serial number for lot-tracked product.",
  f.SZLNTY AS "Line type controlling how the line hits inventory and the ledger (stock, non-stock, freight).",
  f.SZNXTR AS "Next status the line will move to in the order activity flow.",
  f.SZLTTR AS "Last status the line completed in the order activity flow.",
  f.SZUOM AS "Unit of measure the quantity was transacted in.",
  f.SZUORG AS "Ordered or transaction quantity for the line.",
  f.SZUPRC / POWER(10, 4) AS "Unit price for the line; four implied decimals.",  -- implied decimals: 4 (verify in F9210)
  f.SZAEXP / POWER(10, 2) AS "Extended price (quantity times unit price) for the line.",  -- implied decimals: 2 (verify in F9210)
  f.SZUNCS / POWER(10, 4) AS "Unit cost for the line; 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.SZSHPN AS "Shipment number linking the line to the shipment workbench (F4215)."
FROM <catalog>.<schema_data>.f47012 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;