Search Results payment_base_amount

Overview

AMS_AP_PAYMENTS_V is a reporting view owned by the Oracle Marketing (AMS) module that exposes invoice payment information sourced from Oracle Payables. It consolidates data from invoices, payments, checks, payment terms, and lookup codes into a single flattened structure, allowing Marketing users to analyze payment activity associated with invoice records without navigating the multiple underlying Payables tables directly. Although the ETRM documentation for release 12.2.2 records no implementation in the reference database, the view definition is fully documented and remains available for deployments that integrate Marketing with Payables.

The view's role is primarily reporting and integration. It provides a denormalized projection of invoice-to-payment relationships, including check-level status information such as when and by whom a payment was stopped, released, or cleared. This makes it useful for reconciliation, audit, and status tracking across the payment lifecycle.

Underlying Base Objects

The view is defined over several Oracle Payables base tables joined on their primary keys:

• AP_INVOICES — invoice header data; joined to AP_INVOICE_PAYMENTS on INVOICE_ID.
• AP_CHECKS — payment (check) records; joined to AP_INVOICE_PAYMENTS on CHECK_ID.
• AP_INVOICE_PAYMENTS — the intersection table linking invoices to checks.
• AP_TERMS — payment terms; joined on TERMS_ID.
• AP_LOOKUP_CODES (aliased ALC1 through ALC7) — outer-joined to resolve lookup codes into displayed values for invoice type, payment method, payment status, source, pay group, check payment method, and check status.

The lookup code joins use the (+) outer-join syntax, ensuring rows are retained even when a matching lookup value is absent. User ID fields are further decorated with FND_USER_AP_PKG.GET_USER_NAME to translate internal user identifiers into readable names.

Key Columns

Given the user's interest in the term "stopped_at," the view exposes a dedicated set of payment-stop attributes drawn from AP_CHECKS:

• STOPPED_AT, STOPPED_BY, STOPPED_DATE — the payables location at which the payment was stopped, the user who performed the stop (rendered via FND_USER_AP_PKG.GET_USER_NAME), and the stop timestamp.
• RELEASED_AT, RELEASED_BY, RELEASED_DATE — the corresponding release information once a stopped payment is reinstated.
• STATE, STATUS_LOOKUP_CODE, STATUS displayed field — the overall check status.
• CLEARED_AMOUNT, CLEARED_DATE — reconciliation details.
• CHECK_NUMBER, CHECK_DATE, CHECK_AMOUNT, PAYMENT_METHOD_LOOKUP_CODE — payment identification and method.
• AMOUNT, INVOICE_BASE_AMOUNT, PAYMENT_BASE_AMOUNT — monetary values at payment, invoice-base, and payment-base levels.
• VOUCHER_NUM, INVOICE_NUM, INVOICE_CURRENCY_CODE, INVOICE_AMOUNT, AMOUNT_PAID — invoice-level identifiers and amounts.

Common Use Cases and Queries

A frequent scenario is identifying payments that have been stopped and not yet released, which is directly relevant to the searched term "stopped_at." A typical query follows:

• Filter stopped payments: SELECT CHECK_NUMBER, VENDOR_NAME, STOPPED_AT, STOPPED_BY, STOPPED_DATE, RELEASED_DATE FROM AMS_AP_PAYMENTS_V WHERE STOPPED_DATE IS NOT NULL AND RELEASED_DATE IS NULL;
• Reconciliation by status: SELECT VOUCHER_NUM, CHECK_AMOUNT, CLEARED_AMOUNT, CLEARED_DATE FROM AMS_AP_PAYMENTS_V WHERE CHECK_DATE BETWEEN :start_date AND :end_date;
• Audit of stop/release activity by user: SELECT VENDOR_NAME, STOPPED_BY, RELEASED_BY, STOPPED_DATE, RELEASED_DATE FROM AMS_AP_PAYMENTS_V ORDER BY STOPPED_DATE DESC;

Because the view spans multiple joined tables, queries against it should apply selective filters (vendor, date range, or check number) to maintain performance. Data visibility and privileges are inherited from the Payables base objects, so access should be granted carefully in production environments.