F4201

-- ============================================================
-- Table  : F4201 Sales order header — one row per order carrying customer, dates, totals, and shipping terms
-- Purpose: Column-selected read of F4201 — auto-generated from field metadata
-- Grain  : One row per SHKCOO + SHDOCO + SHDCTO
-- 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.SHKCOO AS "Order key company - disambiguates order numbers across companies",
  h.SHDOCO AS "Order number",
  h.SHDCTO AS "Order type code (SO, ST, CO, ...)",
  h.SHSFXO AS "Order suffix, used to split partial shipments/invoices",
  TRIM(h.SHMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  h.SHCO AS "Company the transaction belongs to",
  h.SHOORN AS "Original order number when this order was generated from another",
  h.SHOCTO AS "Original order type",
  h.SHAN8 AS "Sold-to customer address book number",
  h.SHSHAN AS "Ship-to address book number",
  h.SHPA8 AS "Parent address book number",
  h.SHITAN AS "Invoice-to address book number",
  h.SHDVAN AS "Deliver-to address book number",
  CASE WHEN h.SHTRDJ IS NULL OR h.SHTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHTRDJ AS INT) DIV 1000, 1, 1), CAST(h.SHTRDJ AS INT) % 1000 - 1) END AS "Order/transaction date",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHDRQJ IS NULL OR h.SHDRQJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHDRQJ AS INT) DIV 1000, 1, 1), CAST(h.SHDRQJ AS INT) % 1000 - 1) END AS "Date the customer requested the goods",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHRQSJ IS NULL OR h.SHRQSJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHRQSJ AS INT) DIV 1000, 1, 1), CAST(h.SHRQSJ AS INT) % 1000 - 1) END AS "Requested ship date",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHPDDJ IS NULL OR h.SHPDDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHPDDJ AS INT) DIV 1000, 1, 1), CAST(h.SHPDDJ AS INT) % 1000 - 1) END AS "Scheduled pick date",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHOPDJ IS NULL OR h.SHOPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHOPDJ AS INT) DIV 1000, 1, 1), CAST(h.SHOPDJ AS INT) % 1000 - 1) END AS "Original promised delivery date, kept for on-time measurement",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHPPDJ IS NULL OR h.SHPPDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHPPDJ AS INT) DIV 1000, 1, 1), CAST(h.SHPPDJ AS INT) % 1000 - 1) END AS "Currently promised ship date",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHADDJ IS NULL OR h.SHADDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHADDJ AS INT) DIV 1000, 1, 1), CAST(h.SHADDJ AS INT) % 1000 - 1) END AS "Actual ship date",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHADLJ IS NULL OR h.SHADLJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHADLJ AS INT) DIV 1000, 1, 1), CAST(h.SHADLJ AS INT) % 1000 - 1) END AS "Actual delivery date",  -- CYYDDD Julian → DATE
  CASE WHEN h.SHCNDJ IS NULL OR h.SHCNDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(h.SHCNDJ AS INT) DIV 1000, 1, 1), CAST(h.SHCNDJ AS INT) % 1000 - 1) END AS "Date the order or line was canceled",  -- CYYDDD Julian → DATE
  h.SHVR01 AS "Reference field - commonly holds the customer PO number",
  h.SHVR02 AS "Second reference field",
  h.SHPTC AS "Payment terms code",
  h.SHRYIN AS "Payment instrument (check, EFT, credit card, ...)",
  h.SHASN AS "Price and adjustment schedule applied to the order",
  h.SHPRGP AS "Customer pricing group",
  h.SHTRDC / POWER(10, 3) AS "Trade discount percent applied to the whole order",  -- implied decimals: 3 (verify in F9210)
  h.SHTXA1 AS "Tax rate/area",
  h.SHHOLD AS "Hold code - a value here means the order is blocked",
  h.SHCARS AS "Carrier address book number",
  h.SHMOT AS "Mode of transport",
  h.SHROUT AS "Delivery route code",
  h.SHFRTH AS "Freight handling code (who pays/arranges freight)",
  h.SHOTOT / POWER(10, 2) AS "Order total amount, domestic currency",  -- implied decimals: 2 (verify in F9210)
  h.SHTOTC / POWER(10, 2) AS "Order total cost, domestic currency",  -- implied decimals: 2 (verify in F9210)
  h.SHCRCD AS "Transaction (from) currency code",
  h.SHCRR AS "Currency conversion spot rate; implied decimals vary by setup",
  h.SHFAP / POWER(10, 2) AS "Order open amount in foreign currency"  -- implied decimals: 2 (verify in F9210)
  -- … plus 2 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f4201 h
WHERE
  h.SHKCOO = '<KCOO>'
  -- AND h.SHDOCO = <DOCO>
  -- AND h.SHDCTO = '<DCTO>'
  -- AND h.SHTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND h.SHTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY h.SHKCOO;