Search Results ap_user_exchange_rates




Overview
Explore in AI Chat

Overview

AP_USER_EXCHANGE_RATES is a Payables (AP) module table that stores user-defined exchange rates applied when a Payment Process Request (PPR) is submitted across multiple payment currencies and, potentially, multiple functional (ledger) currencies. Because a single payment run can settle invoices denominated in different currencies belonging to different ledgers, Oracle Payables needs a mechanism to capture the specific conversion rate the user wants applied between each ledger currency and each payment currency involved in that run. This table satisfies that requirement by holding one row per currency pairing for a given payment run.

The table is keyed by the composite primary key AP_USER_EXCHANGE_RATES_PK, defined on (CHECKRUN_ID, LEDGER_CURRENCY_CODE, PAYMENT_CURRENCY_CODE). A unique index, AP_USER_EXCHANGE_RATES_U1, exists on (CHECKRUN_ID, PAYMENT_CURRENCY_CODE, LEDGER_CURRENCY_CODE), enforcing the same logical uniqueness in a slightly reordered column sequence. From a Data Vault modeling perspective, the ETRM metadata classifies this object heuristically as standalone. This is best interpreted as a modeling suggestion: the table can be treated as a self-contained keyed structure rather than being decomposed into hub, link, and satellite constructs, since it holds its own composite business key and descriptive attributes without documented foreign-key dependencies.

Key Information Stored

The table contains nine documented columns. The most significant are those that define the identity of a rate and its value:

  • CHECKRUN_ID — Identifier of the payment process request (check run) for which the user rates were entered. This is the primary grouping column and the anchor of both the primary key and the unique index.
  • LEDGER_CURRENCY_CODE — The functional currency of the ledger associated with the transaction being converted. This is the column most directly relevant to the search term "ledger_currency_code" and the answer to most queries against this table.
  • PAYMENT_CURRENCY_CODE — The currency in which the payment is issued. Together with LEDGER_CURRENCY_CODE, it defines the conversion direction.
  • EXCHANGE_RATE — The user-specified rate applied to convert between the ledger currency and the payment currency for this checkrun.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns recording who created and last modified each row and when.

The surrogate primary key is AP_USER_EXCHANGE_RATES_PK on (CHECKRUN_ID, LEDGER_CURRENCY_CODE, PAYMENT_CURRENCY_CODE); the business-key candidate is the unique index AP_USER_EXCHANGE_RATES_U1, which validates the same combination in a different column order.

Common Use Cases and Queries

The primary use case is verifying or auditing whether a user-entered exchange rate was captured for a given payment run, and confirming the rate applied between a specific ledger currency and payment currency pairing.

  • Retrieve rates for a payment run:
    SELECT checkrun_id, ledger_currency_code, payment_currency_code, exchange_rate
    FROM ap.ap_user_exchange_rates
    WHERE checkrun_id = :checkrun_id;
  • Find the rate for a specific currency pairing:
    SELECT exchange_rate
    FROM ap.ap_user_exchange_rates
    WHERE checkrun_id = :checkrun_id
    AND ledger_currency_code = :ledger_currency_code
    AND payment_currency_code = :payment_currency_code;
  • Audit trail of who entered rates:
    SELECT created_by, creation_date, last_updated_by, last_update_date
    FROM ap.ap_user_exchange_rates
    WHERE checkrun_id = :checkrun_id;
  • Reporting on multi-currency payment runs: join to the check run identifier to summarize which ledgers and currencies required user-maintained rates, useful for treasury and FX analysis.

Because the table is keyed by checkrun and currency codes, query performance is optimal when filtering on CHECKRUN_ID first, which aligns with the leading column of both the primary key and unique index.

Related Objects

While the ETRM metadata classifies this table as standalone, in practice it participates in the payment processing flow through its checkrun identifier and currency references:

  • AP_CHECKS_ALL — Payment records created by the check run; joined via CHECKRUN_ID to correlate rates with the resulting payments.
  • AP_INVOICE_PAYMENTS_ALL — Links paid invoices to payments, enabling verification of which invoices drove the currency conversions.
  • AP_SELECTED_INVOICES_ALL — Invoices selected for a payment run, associated with the same checkrun.
  • FND_CURRENCIES — Reference table for PAYMENT_CURRENCY_CODE and LEDGER_CURRENCY_CODE values.
  • GL_LEDGERS — Source of ledger functional currency used to interpret LEDGER_CURRENCY_CODE.
  • AP_USER_EXCHANGE_RATES_PK / _U1 — The primary key and unique index that govern row uniqueness.

Together these objects allow the user-entered rate to be traced from the payment run through to the settled invoices and the ledger's functional currency.