F47036

-- ============================================================
-- Table  : F47036 One row per outbound advance ship notice (X12 856) at the shipment level, written by the R47032 extraction at the top hierarchy break.
-- Purpose: Column-selected read of F47036 — 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.SYSPID AS "Shipment identifier; the outbound 856 extraction writes one header row per shipment at the highest hierarchy break.",
  f.SYACKCD AS "Acknowledgment code returned for the document.",
  CASE WHEN f.SYACKDJ IS NULL OR f.SYACKDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYACKDJ AS INT) DIV 1000, 1, 1), CAST(f.SYACKDJ AS INT) % 1000 - 1) END AS "Julian date the acknowledgment was received.",  -- CYYDDD Julian → DATE
  f.SYACKRC AS "Flag showing whether an acknowledgment has been received for this document.",
  f.SYACKT AS "Acknowledgment type reported by the trading partner.",
  CASE WHEN f.SYADLJ IS NULL OR f.SYADLJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYADLJ AS INT) DIV 1000, 1, 1), CAST(f.SYADLJ AS INT) % 1000 - 1) END AS "Actual delivery date (Julian).",  -- CYYDDD Julian → DATE
  f.SYADTM AS "Actual shipment time.",
  CASE WHEN f.SYASNDJ IS NULL OR f.SYASNDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.SYASNDJ AS INT) DIV 1000, 1, 1), CAST(f.SYASNDJ AS INT) % 1000 - 1) END AS "Julian date the advance ship notice was generated.",  -- CYYDDD Julian → DATE
  f.SYASNTM AS "Time the advance ship notice was generated.",
  f.SYDCT4 AS "Document type of the related document at the shipment level.",
  f.SYDOC1 AS "Related document number (order or invoice) carried at the shipment level.",
  f.SYDVAN AS "Deliver-to address book number.",
  f.SYEDITM AS "Time the EDI transmission was created.",
  f.SYHOLD AS "Hold code applied when the staged order needs review before release.",
  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
FROM <catalog>.<schema_data>.f47036 f
WHERE
  f.SYEDDT >= <EDDT_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.SYEDDT <= <EDDT_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.SYEKCO;