F4209

-- ============================================================
-- Table  : F4209 Held orders file - one row per order or line currently or previously on hold, including who released it and when
-- Purpose: Column-selected read of F4209 — auto-generated from field metadata
-- Grain  : One row per HOHCOD + HODCTO + HODOCO + HOKCOO + HOSFXO + HOLNID + HODLNID + HORDJ + HORDT + HOASTS + HORPER
-- 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.HOHCOD AS "Hold code placed on the order",
  f.HODCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  f.HODOCO AS "Order number",
  f.HOKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  f.HOSFXO AS "Order suffix distinguishing multiple documents under one order number",
  f.HOLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  f.HODLNID / POWER(10, 3) AS "Distribution line number the hold applies to",  -- implied decimals: 3 (verify in F9210)
  CASE WHEN f.HORDJ IS NULL OR f.HORDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.HORDJ AS INT) DIV 1000, 1, 1), CAST(f.HORDJ AS INT) % 1000 - 1) END AS "Date the hold was released",  -- CYYDDD Julian → DATE
  f.HORDT AS "Time the hold was released",
  f.HOASTS AS "Approval status of the held order",
  f.HORPER AS "Address number of the person responsible for reviewing the hold",
  f.HOAN8 AS "Address book number of the customer or supplier on the held order",
  TRIM(f.HOMCU) AS "Branch/plant business unit; right-justified, space-padded to 12",
  f.HOITM AS "Short internal item number - joins to F4101/F4102",
  f.HOLITM AS "Second item number, the long code users normally key",
  f.HOAITM AS "Third item number (catalog or alternate identifier)",
  CASE WHEN f.HOTRDJ IS NULL OR f.HOTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.HOTRDJ AS INT) DIV 1000, 1, 1), CAST(f.HOTRDJ AS INT) % 1000 - 1) END AS "Order date of the held document",  -- CYYDDD Julian → DATE
  CASE WHEN f.HODRQJ IS NULL OR f.HODRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.HODRQJ AS INT) DIV 1000, 1, 1), CAST(f.HODRQJ AS INT) % 1000 - 1) END AS "Requested date on the held order",  -- CYYDDD Julian → DATE
  CASE WHEN f.HOPDDJ IS NULL OR f.HOPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.HOPDDJ AS INT) DIV 1000, 1, 1), CAST(f.HOPDDJ AS INT) % 1000 - 1) END AS "Scheduled pick/delivery date on the held order",  -- CYYDDD Julian → DATE
  f.HOCTYP AS "Code type qualifying what kind of hold this is",
  f.HORDC AS "Release code recorded when the hold is lifted",
  f.HORDB AS "User who released the hold",
  f.HOARTG AS "Approval routing code for approval-hold processing",
  f.HOATY AS "Type of approver required",
  f.HOEDEI AS "EDI discrepancy error identifier",
  f.HOPA8 AS "Parent address book number",
  f.HOSHAN AS "Ship-to address book number"
FROM <catalog>.<schema_data>.f4209 f
WHERE
  f.HODCTO = '<DCTO>'
  -- AND f.HODOCO = <DOCO>
  -- AND f.HOKCOO = '<KCOO>'
  -- AND f.HOTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.HOTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.HOHCOD;