Results for “rev_pmt_hist_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AP_PAYMENT_HISTORY_ALL is a Payables module table in the AP schema that stores maturity and reconciliation history for payments. It records the accounting lifecycle of a payment instrument — typically a check or electronic payment — as that instrument moves through bank clearing, reconciliation, exchange-rate revaluation, and gain/loss recognition. The table is populated and maintained by Oracle Payables' payment reconciliation and accounting processes, and it serves as the historical audit trail behind the current state of records in AP_CHECKS_ALL.

Within a Data Vault modeling context, the documented foreign-key structure suggests a satellite-leaning classification. The table hangs off AP_CHECKS_ALL and AP_ACCOUNTING_EVENTS_ALL via foreign keys, and it carries a self-referencing key on PAYMENT_HISTORY_ID, which is characteristic of a descriptive/history satellite attached to a core hub-and-link backbone rather than a hub or link in its own right. This classification is a heuristic derived from the FK topology and should be treated as a modeling suggestion rather than a certified Data Vault label.

Key Information Stored

The primary key is AP_PAYMENT_HISTORY_PK, defined on the surrogate key column PAYMENT_HISTORY_ID. Two documented unique indexes serve as business-key candidates: AP_PAYMENT_HISTORY_U1 on PAYMENT_HISTORY_ID and AP_PAYMENT_HISTORY_U2 on REV_PMT_HIST_ID, the latter supporting reversal-to-original linkage. Among the 48 documented columns, the most significant are:

• PAYMENT_HISTORY_ID — surrogate primary key identifying each history row.
• CHECK_ID — foreign key to AP_CHECKS_ALL, anchoring the history to the payment instrument.
• ACCOUNTING_EVENT_ID — foreign key to AP_ACCOUNTING_EVENTS_ALL, tying the row to a specific subledger accounting event.
• ACCOUNTING_DATE and TRANSACTION_TYPE — the effective accounting date and the nature of the transaction recorded.
• POSTED_FLAG and MATCHED_FLAG — status indicators for posting and reconciliation matching.
• ORG_ID — the operating unit, which drives multi-org security and reporting isolation.
• REV_PMT_HIST_ID — self-referencing column linking a reversal row to its original history row; also a unique business-key candidate.
• TRX_BANK_AMOUNT, ERRORS_BANK_AMOUNT, CHARGES_BANK_AMOUNT — transaction, error, and charge amounts expressed in the bank currency.
• TRX_PMT_AMOUNT, ERRORS_PMT_AMOUNT, CHARGES_PMT_AMOUNT — equivalent amounts in the payment currency.
• TRX_BASE_AMOUNT, ERRORS_BASE_AMOUNT, CHARGES_BASE_AMOUNT — equivalent amounts in the ledger base currency.
• BANK_TO_BASE_XRATE and PMT_TO_BASE_XRATE (with their TYPE/DATE companions) — the exchange rates applied to translate bank and payment currency amounts into base currency.
• MRC_* columns — reporting-currency (MRC) equivalents of the base-currency amounts and rates, populated only when multiple reporting currencies are enabled.
• GAIN_LOSS_INDICATOR, RELATED_EVENT_ID, INVOICE_ADJUSTMENT_EVENT_ID, HISTORICAL_FLAG — flags and links supporting realized gain/loss recognition and historical/archival tracking.
• Standard WHO audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and the PROGRAM_/REQUEST_ID concurrency columns.

Common Use Cases and Queries

Typical uses include payment reconciliation reporting, bank clearing analysis, realized exchange gain/loss reporting, and audit of payment accounting events. A common pattern joins the history back to the payment instrument:

• Reconciliation status by payment: join AP_PAYMENT_HISTORY_ALL to AP_CHECKS_ALL on CHECK_ID, filtering on MATCHED_FLAG and POSTED_FLAG.
• Gain/loss analysis: filter on GAIN_LOSS_INDICATOR = 'Y' and aggregate the base-currency error/charge amounts by accounting period.
• Reversal tracing: self-join on PAYMENT_HISTORY_ID = REV_PMT_HIST_ID to link reversals to originals.
• Multi-currency revaluation: compare TRX_BANK_AMOUNT, TRX_PMT_AMOUNT, and TRX_BASE_AMOUNT alongside the stored BANK_TO_BASE_XRATE and PMT_TO_BASE_XRATE.
• Accounting event audit: join to AP_ACCOUNTING_EVENTS_ALL on ACCOUNTING_EVENT_ID to reconcile the event to its journal entries.

Queries should generally be constrained by ORG_ID and ACCOUNTING_DATE to limit scan size on this high-volume table.

Related Objects

• AP_CHECKS_ALL — joined on AP_PAYMENT_HISTORY_ALL.CHECK_ID = AP_CHECKS_ALL.CHECK_ID; the primary payment header.
• AP_ACCOUNTING_EVENTS_ALL — joined on ACCOUNTING_EVENT_ID; the subledger accounting event definition.
• AP_PAYMENT_HISTORY_ALL (self) — joined on PAYMENT_HISTORY_ID = REV_PMT_HIST_ID for reversal linkage.
• AP_INVOICE_PAYMENTS_ALL — the invoice-to-payment link, useful when tracing history back to invoices.
• AP_INVOICES_ALL — the source invoice, reachable through AP_INVOICE_PAYMENTS_ALL.
• GL_JE_HEADERS / GL_JE_LINES — the general ledger journals created from the referenced accounting events.