P4310

-- ============================================================
-- Program: P4310 Purchase Order Entry — create and revise purchase orders, requisitions, and quotes (header and detail lines)
-- Purpose: Purchase Order Entry — create and revise purchase orders, requisitions, and quotes (header and detail lines) — auto-generated boilerplate from program-table-map
-- Grain  : F4301 × F4311, F0101, F0401, F4101, F4102, F4105, F41061, F0014 — 1:N joins yield one row per detail line
-- Tables : F4301, F4311, F0101, F0401, F4101, F4102, F4105, F41061, F0014
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). 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",
  d.PDLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  ab.ABAN8 AS "Address book number - the master key for any entity",
  f.A6AN8 AS "Supplier address book number - joins to F0101",
  im.IMITM AS "Short item number - the internal numeric item key",
  ib.IBITM AS "Short item number - the internal numeric item key",
  TRIM(ib.IBMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  f4.COITM AS "Short internal item number (8-digit numeric key).",
  TRIM(f4.COMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f4.COLOCN AS "Storage location within the branch, formatted per the branch location structure.",
  f4.COLOTN AS "Lot or serial number identifying the specific lot of stock.",
  f4.COLEDG AS "Cost method code (e.g. 01 last-in, 02 weighted average, 07 standard) selecting which cost applies.",
  TRIM(f41.CBMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f41.CBAN8 AS "Supplier address number the catalog price belongs to.",
  f41.CBITM AS "Short internal item number (8-digit numeric key).",
  f41.CBCATN AS "Supplier catalog name grouping this set of prices.",
  f41.CBCRCD AS "Transaction currency code of the price.",
  f41.CBUOM AS "Unit of measure the price applies to, as entered.",
  f41.CBUORG AS "Quantity break threshold: the price applies at or above this quantity.",
  CASE WHEN f41.CBEXDJ IS NULL OR f41.CBEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f41.CBEXDJ AS INT) DIV 1000, 1, 1), CAST(f41.CBEXDJ AS INT) % 1000 - 1) END AS "Expiration date; the record stops applying after this date.",  -- CYYDDD Julian → DATE
  f0.PNPTC AS "Payment terms code referenced by customers, suppliers, invoices, and vouchers."
FROM <catalog>.<schema_data>.f4301 h
LEFT JOIN <catalog>.<schema_data>.f4311 d
  ON d.PDDOCO = h.PHDOCO
 AND d.PDKCOO = h.PHKCOO
 AND d.PDDCTO = h.PHDCTO
 AND d.PDSFXO = h.PHSFXO
LEFT JOIN <catalog>.<schema_data>.f0101 ab
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f0401 f
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f4101 im
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f4105 f4
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f41061 f41
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f0014 f0
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
WHERE
  h.PHKCOO = '<KCOO>'
  AND h.PHDOCO = <DOCO>
  AND h.PHDCTO = '<DCTO>'
ORDER BY h.PHKCOO;