F47046

-- ============================================================
-- Table  : F47046 One row per outbound EDI invoice (X12 810) header extracted from billed sales orders; the staging copy of what was billed to the customer.
-- Purpose: Column-selected read of F47046 — 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 sales order number the invoice bills; 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.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
  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.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.",
  f.SYDCT4 AS "Document type of the A/R invoice (RI and related types).",
  f.SYDOC1 AS "A/R invoice number extracted into the 810; the join to F03B11.",
  f.SYITAN AS "Invoiced-to address book number."
FROM <catalog>.<schema_data>.f47046 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;