P4312

-- ============================================================
-- Program: P4312 PO Receipts — record receipt of goods against purchase orders, updating on-hand inventory and creating receiver records
-- Purpose: PO Receipts — record receipt of goods against purchase orders, updating on-hand inventory and creating receiver records — auto-generated boilerplate from program-table-map
-- Grain  : F43121 × F4311, F4111, F41021, F0911, F4301, F4102 — 1:N joins yield one row per detail line
-- Tables : F43121, F4311, F4111, F41021, F0911, F4301, F4102
-- 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.PRMATC AS "Match record type - separates receipt rows from voucher-match rows",
  h.PRDOCO AS "Order number",
  h.PRDCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  h.PRKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  h.PRSFXO AS "Order suffix distinguishing multiple documents under one order number",
  h.PRLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  h.PRNLIN AS "Sequence number of this receipt/match event against the PO line",
  h.PRDOC AS "Voucher document number created by the match",
  il.ILUKID AS "Unique key ID - the system-assigned surrogate key",
  loc.LIITM AS "Short item number - the internal numeric item key",
  TRIM(loc.LIMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  loc.LILOCN AS "Storage location within the branch plant",
  loc.LILOTN AS "Lot or serial number",
  f.GLDCT AS "Document type of the journal entry (JE, PV, RI, etc.).",
  f.GLKCO AS "Company that assigned the document number (key company).",
  CASE WHEN f.GLDGJ IS NULL OR f.GLDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.GLDGJ AS INT) DIV 1000, 1, 1), CAST(f.GLDGJ AS INT) % 1000 - 1) END AS "G/L date driving the fiscal period the line posts to.",  -- CYYDDD Julian → DATE
  f.GLJELN AS "Line number within the journal entry.",
  f.GLEXTL AS "Line extension code completing the unique key.",
  f.GLLT AS "Ledger type the line belongs to (AA actual, CA foreign currency, etc.).",
  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)"
FROM <catalog>.<schema_data>.f43121 h
LEFT JOIN <catalog>.<schema_data>.f4311 d
  ON d.PDDOCO = h.PRDOCO
 AND d.PDKCOO = h.PRKCOO
 AND d.PDDCTO = h.PRDCTO
 AND d.PDSFXO = h.PRSFXO
 AND d.PDLNID = h.PRLNID
LEFT JOIN <catalog>.<schema_data>.f4111 il
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f41021 loc
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
LEFT JOIN <catalog>.<schema_data>.f0911 f
  ON f.GLDOC = h.PRDOC
LEFT JOIN <catalog>.<schema_data>.f4301 f4
  ON f4.PHKCOO = h.PRKCOO
 AND f4.PHDOCO = h.PRDOCO
 AND f4.PHDCTO = h.PRDCTO
 AND f4.PHSFXO = h.PRSFXO
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
WHERE
  h.PRDOCO = <DOCO>
  AND h.PRDCTO = '<DCTO>'
  AND h.PRKCOO = '<KCOO>'
ORDER BY h.PRDOCO;