F47021

-- ============================================================
-- Table  : F47021 One row per inbound EDI purchase order acknowledgment (X12 855) from a supplier, staged against a JDE purchase order header.
-- Purpose: Column-selected read of F47021 — auto-generated from field metadata
-- Grain  : One row per SYEKCO + SYEDOC + SYEDCT
-- 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.SYEKCO AS "Key company that scopes the EDI document number; part of the staging primary key.",
  f.SYEDOC AS "EDI document number assigned when the transaction is staged; the primary handle for one staged EDI document.",
  f.SYEDCT AS "EDI document type qualifier; pairs with document number and key company to complete the staging key.",
  f.SYEDST AS "X12 transaction-set number this row stages (850, 855, 856, 810).",
  CASE WHEN f.SYEDDT IS NULL OR f.SYEDDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYEDDT AS INT) DIV 1000, 1, 1), CAST(f.SYEDDT AS INT) % 1000 - 1) END AS "Julian date the transmission was created or received by the EDI translator.",  -- CYYDDD Julian → DATE
  f.SYEDER AS "Direction flag: R for documents received from the trading partner, S for documents JDE is sending.",
  f.SYEDDL AS "Count of detail lines belonging to this EDI document.",
  f.SYEDSP 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.SYEDBT AS "Batch number grouping documents staged in the same translator run.",
  f.SYPNID AS "Trading partner identifier agreed with the customer or supplier; the natural grain for partner-level EDI scorecards.",
  f.SYTPUR AS "Purpose of the transaction set: original, change, cancellation, or response.",
  f.SYKCOO AS "Company segment of the JDE order number key.",
  f.SYDOCO AS "JDE purchase order number being acknowledged; the join back to F4301/F4311.",
  f.SYDCTO AS "JDE order type of the related order (SO, OP, and so on).",
  f.SYSFXO AS "Order suffix; distinguishes multiple documents against the same order number.",
  TRIM(f.SYMCU) AS "Branch/plant on the order; right-justified 12-character business unit.",
  f.SYAN8 AS "Supplier address book number on the acknowledged purchase order.",
  f.SYSHAN AS "Ship-to address book number.",
  CASE WHEN f.SYDRQJ IS NULL OR f.SYDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYDRQJ AS INT) DIV 1000, 1, 1), CAST(f.SYDRQJ AS INT) % 1000 - 1) END AS "Requested date on the order line or document (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.SYTRDJ IS NULL OR f.SYTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYTRDJ AS INT) DIV 1000, 1, 1), CAST(f.SYTRDJ AS INT) % 1000 - 1) END AS "Order or transaction date (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.SYADDJ IS NULL OR f.SYADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYADDJ AS INT) DIV 1000, 1, 1), CAST(f.SYADDJ AS INT) % 1000 - 1) END AS "Actual ship date (Julian).",  -- CYYDDD Julian → DATE
  CASE WHEN f.SYPPDJ IS NULL OR f.SYPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYPPDJ AS INT) DIV 1000, 1, 1), CAST(f.SYPPDJ AS INT) % 1000 - 1) END AS "Promised shipment date (Julian).",  -- CYYDDD Julian → DATE
  f.SYVR01 AS "Free-form customer reference; on customer-facing documents this usually carries the customer's PO number.",
  f.SYOTOT / POWER(10, 2) AS "Gross document amount staged for the transaction.",  -- implied decimals: 2 (verify in F9210)
  f.SYCORD AS "Change order sequence number on the acknowledged order.",
  f.SYCRCD AS "Transaction currency code.",
  f.SYACKT AS "Acknowledgment type reported by the trading partner."
FROM <catalog>.<schema_data>.f47021 f
WHERE
  f.SYTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.SYTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.SYEKCO;