F47011

-- ============================================================
-- Table  : F47011 One row per inbound EDI customer purchase order (X12 850) waiting to become a JDE sales order header; staging mirror of F4201.
-- Purpose: Column-selected read of F47011 — 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.SYEDTY AS "Identifies which record layout this row carries inside the EDI document (header, detail, address, text).",
  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 sales order number created or matched when the 850 is processed; the join to F4201/F4211.",
  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.SYCO AS "Company that owns the transaction.",
  f.SYAN8 AS "Primary address book number on the document: customer sold-to on sales documents, supplier on purchasing documents.",
  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.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.SYPTC AS "Payment terms code applied to the document.",
  f.SYFRTH AS "Freight handling code describing who arranges and pays freight.",
  f.SYOTOT / POWER(10, 2) AS "Gross document amount staged for the transaction.",  -- implied decimals: 2 (verify in F9210)
  f.SYCRCD AS "Transaction currency code.",
  f.SYCRR AS "Spot exchange rate used to convert the transaction currency; implied decimals vary by setup.",
  CASE WHEN f.SYRQSJ IS NULL OR f.SYRQSJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYRQSJ AS INT) DIV 1000, 1, 1), CAST(f.SYRQSJ AS INT) % 1000 - 1) END AS "Requested ship date (Julian).",  -- CYYDDD Julian → DATE
  f.SYHOLD AS "Hold code applied when the staged order needs review before release."
FROM <catalog>.<schema_data>.f47011 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;