F47047

-- ============================================================
-- Table  : F47047 One row per line on an outbound EDI invoice (810); billed quantities and prices by sales order line, staged for transmission.
-- Purpose: Column-selected read of F47047 — 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 the invoice line bills; 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.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.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.SZADDJ IS NULL OR f.SZADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SZADDJ AS INT) DIV 1000, 1, 1), CAST(f.SZADDJ AS INT) % 1000 - 1) END AS "Actual ship date (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.SZIVD IS NULL OR f.SZIVD = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SZIVD AS INT) DIV 1000, 1, 1), CAST(f.SZIVD AS INT) % 1000 - 1) END AS "Invoice 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.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.SZSOQS AS "Quantity shipped on 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.SZKCO AS "Document company of the A/R invoice key; joins to F03B11 KCO.",
  f.SZDOC AS "A/R invoice number for the line; joins to F03B11 DOC.",
  f.SZDCT AS "A/R invoice document type; joins to F03B11 DCT.",
  f.SZCRCD AS "Transaction currency code.",
  f.SZSHPN AS "Shipment number linking the line to the shipment workbench (F4215)."
FROM <catalog>.<schema_data>.f47047 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;