F0413

-- ============================================================
-- Table  : F0413 A/P matching document: one row per supplier payment (check, EFT, draft) with the payment amount, bank account, dates, and void/reconciliation state.
-- Purpose: Column-selected read of F0413 — auto-generated from field metadata
-- Grain  : One row per RMPYID
-- 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.RMPYID AS "System-assigned payment ID — the key detail application rows hang off",
  f.RMDCTM AS "Matching document type of the payment (PK automatic, PN manual)",
  f.RMDOCM AS "Payment document number, e.g. the check or EFT number",
  f.RMPYE AS "Payee address book number the payment went to",
  f.RMGLBA AS "Short account ID of the bank account the payment was drawn on",
  CASE WHEN f.RMDMTJ IS NULL OR f.RMDMTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RMDMTJ AS INT) DIV 1000, 1, 1), CAST(f.RMDMTJ AS INT) % 1000 - 1) END AS "Payment date",  -- CYYDDD Julian → DATE
  CASE WHEN f.RMVDGJ IS NULL OR f.RMVDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RMVDGJ AS INT) DIV 1000, 1, 1), CAST(f.RMVDGJ AS INT) % 1000 - 1) END AS "G/L date of the void when the payment was voided",  -- CYYDDD Julian → DATE
  f.RMICU AS "Batch number of the payment batch",
  f.RMICUT AS "Batch type (K automatic payments, M manual)",
  CASE WHEN f.RMDICJ IS NULL OR f.RMDICJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RMDICJ AS INT) DIV 1000, 1, 1), CAST(f.RMDICJ AS INT) % 1000 - 1) END AS "Batch date",  -- CYYDDD Julian → DATE
  f.RMPAAP / POWER(10, 2) AS "Payment amount in domestic currency",  -- implied decimals: 2 (verify in F9210)
  f.RMCRCD AS "Currency the payment was issued in",
  f.RMCRRM AS "Currency mode — domestic or foreign entry",
  CASE WHEN f.RMVLDT IS NULL OR f.RMVLDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.RMVLDT AS INT) DIV 1000, 1, 1), CAST(f.RMVLDT AS INT) % 1000 - 1) END AS "Value/cleared date for bank reconciliation and cash positioning",  -- CYYDDD Julian → DATE
  f.RMPYIN AS "Payment instrument (check, EFT, draft)",
  f.RMISTP AS "Payment post status",
  f.RMCBNK AS "Supplier's bank account number on the payment",
  f.RMBKTR AS "Bank tape reconciliation reference from the cleared-payment feed",
  f.RMRCND AS "Reconciled code set by bank statement reconciliation"
FROM <catalog>.<schema_data>.f0413 f
ORDER BY f.RMPYID;