F43121

-- ============================================================
-- Table  : F43121 Purchase order receiver - receipt and voucher-match rows linking PO lines to inventory receipts and A/P vouchers (the 3-way match backbone)
-- Purpose: Column-selected read of F43121 — auto-generated from field metadata
-- Grain  : One row per PRMATC + PRDOCO + PRDCTO + PRKCOO + PRSFXO + PRLNID + PRNLIN + PRDOC
-- 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.PRMATC AS "Match record type - separates receipt rows from voucher-match rows",
  f.PRDOCO AS "Order number",
  f.PRDCTO AS "Order document type (e.g. OP purchase order, SO sales order)",
  f.PRKCOO AS "Company that owns the order-number sequence; part of the composite order key",
  f.PRSFXO AS "Order suffix distinguishing multiple documents under one order number",
  f.PRLNID / POWER(10, 3) AS "Order line number (3 implied decimals, e.g. 1.000)",  -- implied decimals: 3 (verify in F9210)
  f.PRNLIN AS "Sequence number of this receipt/match event against the PO line",
  f.PRDOC AS "Voucher document number created by the match",
  f.PRDCT AS "Voucher document type",
  f.PRKCO AS "Voucher document company",
  f.PRSFX AS "Voucher pay item",
  CASE WHEN f.PRDGL IS NULL OR f.PRDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.PRDGL AS INT) DIV 1000, 1, 1), CAST(f.PRDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  f.PRAN8 AS "Supplier address book number",
  f.PRITM AS "Short internal item number - joins to F4101/F4102",
  f.PRLITM AS "Second item number, the long code users normally key",
  TRIM(f.PRMCU) AS "Branch/plant business unit; right-justified, space-padded to 12",
  TRIM(f.PRIMCU) AS "Item branch/plant the goods were received into; right-justified, space-padded to 12",
  f.PRLOCN AS "Warehouse storage location",
  f.PRLOTN AS "Lot or serial number",
  f.PRLNTY AS "Line type controlling G/L, inventory, and A/P-A/R interfaces",
  f.PRGLC AS "G/L offset class used to derive distribution accounts",
  f.PRLTTR AS "Last completed status in the order activity flow",
  f.PRNXTR AS "Next expected status in the order activity flow",
  CASE WHEN f.PRTRDJ IS NULL OR f.PRTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.PRTRDJ AS INT) DIV 1000, 1, 1), CAST(f.PRTRDJ AS INT) % 1000 - 1) END AS "PO transaction date carried onto the receipt",  -- CYYDDD Julian → DATE
  CASE WHEN f.PRRCDJ IS NULL OR f.PRRCDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.PRRCDJ AS INT) DIV 1000, 1, 1), CAST(f.PRRCDJ AS INT) % 1000 - 1) END AS "Receipt date",  -- CYYDDD Julian → DATE
  f.PRVINV AS "Supplier's invoice number",
  f.PRPST AS "Pay status of the matched voucher",
  f.PRRCD AS "Reason code on the receipt or reversal",
  f.PRUOM AS "Transaction unit of measure",
  f.PRUORG AS "Ordered quantity in the transaction unit of measure",
  f.PRUOPN AS "Quantity received but not yet vouchered (open for match)",
  f.PRUREC AS "Quantity received to date",
  f.PRUCLO AS "Quantity closed without full receipt or match",
  f.PRQTYR AS "Quantity returned",
  f.PRQTYJ AS "Quantity rejected",
  f.PRPRRC / POWER(10, 4) AS "Unit cost in domestic currency (4 implied decimals)",  -- implied decimals: 4 (verify in F9210)
  f.PRECST / POWER(10, 2) AS "Extended cost (quantity x unit cost) in domestic currency",  -- implied decimals: 2 (verify in F9210)
  f.PRAPTD / POWER(10, 2) AS "Amount vouchered/paid to date against this receipt",  -- implied decimals: 2 (verify in F9210)
  f.PRAOPN / POWER(10, 2) AS "Received-not-vouchered amount still open",  -- implied decimals: 2 (verify in F9210)
  f.PRAREC / POWER(10, 2) AS "Amount received to date"  -- implied decimals: 2 (verify in F9210)
  -- … plus 7 more columns — full list in the Fields section above
FROM <catalog>.<schema_data>.f43121 f
WHERE
  f.PRDOCO = <DOCO>
  -- AND f.PRDCTO = '<DCTO>'
  -- AND f.PRKCOO = '<KCOO>'
  -- AND f.PRTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND f.PRTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY f.PRMATC;