F4301

-- ============================================================
-- Table  : F4301 Purchase order header - one row per procurement document carrying supplier, order-level dates, totals, terms, and currency
-- Purpose: Column-selected read of F4301 — auto-generated from field metadata
-- Grain  : One row per PHKCOO + PHDOCO + PHDCTO + PHSFXO
-- 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
  h.PHKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  h.PHDOCO AS "Order number",
  h.PHDCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  h.PHSFXO AS "Order suffix distinguishing multiple documents under one order number",
  TRIM(h.PHMCU) AS "Business unit that owns the order header; right-justified, space-padded to 12",
  h.PHOKCO AS "Company key of the originating order",
  h.PHOORN AS "Order number of the originating document this record came from",
  h.PHOCTO AS "Document type of the originating order",
  h.PHRKCO AS "Company key of the related order",
  h.PHRORN AS "Cross-referenced related order number (PO, SO, or work order)",
  h.PHRCTO AS "Document type of the related order",
  h.PHAN8 AS "Supplier address book number",
  h.PHSHAN AS "Ship-to address book number",
  CASE WHEN h.PHTRDJ IS NULL OR h.PHTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHTRDJ AS INT) DIV 1000, 1, 1), CAST(h.PHTRDJ AS INT) % 1000 - 1) END AS "Order date",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHDRQJ IS NULL OR h.PHDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHDRQJ AS INT) DIV 1000, 1, 1), CAST(h.PHDRQJ AS INT) % 1000 - 1) END AS "Requested date",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHPDDJ IS NULL OR h.PHPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHPDDJ AS INT) DIV 1000, 1, 1), CAST(h.PHPDDJ AS INT) % 1000 - 1) END AS "Scheduled/promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHOPDJ IS NULL OR h.PHOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHOPDJ AS INT) DIV 1000, 1, 1), CAST(h.PHOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHADDJ IS NULL OR h.PHADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHADDJ AS INT) DIV 1000, 1, 1), CAST(h.PHADDJ AS INT) % 1000 - 1) END AS "Actual ship date recorded on the header",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHCNDJ IS NULL OR h.PHCNDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHCNDJ AS INT) DIV 1000, 1, 1), CAST(h.PHCNDJ AS INT) % 1000 - 1) END AS "Cancel date",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHPEFJ IS NULL OR h.PHPEFJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHPEFJ AS INT) DIV 1000, 1, 1), CAST(h.PHPEFJ AS INT) % 1000 - 1) END AS "Price effective date used for supplier price selection",  -- CYYDDD Julian → DATE
  CASE WHEN h.PHPPDJ IS NULL OR h.PHPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.PHPPDJ AS INT) DIV 1000, 1, 1), CAST(h.PHPPDJ AS INT) % 1000 - 1) END AS "Promised ship date",  -- CYYDDD Julian → DATE
  h.PHHOLD AS "Order hold code",
  h.PHPTC AS "Payment terms code",
  h.PHTXA1 AS "Tax rate/area",
  h.PHEXR1 AS "Tax explanation code",
  h.PHFRTH AS "Freight handling code",
  h.PHANBY AS "Buyer address book number",
  h.PHANCR AS "Carrier address book number",
  h.PHMOT AS "Mode of transport",
  h.PHOTOT / POWER(10, 2) AS "Gross total amount of the whole order",  -- implied decimals: 2 (verify in F9210)
  h.PHCRRM AS "Currency entry mode - order entered in foreign or domestic currency",
  h.PHCRCD AS "Transaction currency code",
  h.PHCRR AS "Currency conversion spot rate; implied decimals vary by setup",
  h.PHFAP / POWER(10, 2) AS "Open amount in foreign currency",  -- implied decimals: 2 (verify in F9210)
  h.PHBCRC AS "Base (domestic) currency of the company",
  h.PHORBY AS "User recorded as having placed the order",
  h.PHTKBY AS "User who entered the order",
  h.PHCORD AS "Change order sequence number",
  h.PHMATY AS "Match type controlling two-way vs three-way voucher match"
FROM <catalog>.<schema_data>.f4301 h
WHERE
  h.PHKCOO = '<KCOO>'
  -- AND h.PHDOCO = <DOCO>
  -- AND h.PHDCTO = '<DCTO>'
  -- AND h.PHTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND h.PHTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY h.PHKCOO;