F47037

-- ============================================================
-- Table  : F47037 One row per hierarchy line of an outbound 856 (order, pack, or item level) under a shipment header; sales-order lines plus SSCC/UPC pack identifiers.
-- Purpose: Column-selected read of F47037 — 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.SZSPID AS "Shipment identifier; the outbound 856 extraction writes one header row per shipment at the highest hierarchy break.",
  f.SZHLVL AS "Hierarchical level number of this row in the 856 structure (shipment, order, pack, item).",
  f.SZHL03 AS "Hierarchical level code naming what this 856 level represents (S, O, T, P, I).",
  f.SZKCOO AS "Company segment of the JDE order number key.",
  f.SZDOCO AS "JDE sales order number the shipped line came from; 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
  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.SZLOTN AS "Lot or serial number for lot-tracked product.",
  f.SZUORG AS "Ordered or transaction quantity for the line.",
  f.SZSOQS AS "Quantity shipped on the line.",
  f.SZCARS AS "Carrier address book number.",
  f.SZMOT AS "Mode of transport for the shipment.",
  f.SZSHPN AS "Shipment number linking the line to the shipment workbench (F4215).",
  f.SZUPCN AS "UPC code identifying the consumer unit staged in the ship notice.",
  f.SZSCCN AS "Shipping container (SCC-14) code for the pack level of the 856.",
  f.SZPAK AS "SSCC-18 serialized pack number carried in the ship notice hierarchy."
FROM <catalog>.<schema_data>.f47037 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;