Search Results ap_invoice_payments_all




The AP_INVOICE_PAYMENTS_ALL table in Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2 is a critical repository for storing payment-related information for supplier invoices. As part of the Accounts Payable (AP) module, this table captures transactional data associated with invoice payments, including payment amounts, dates, methods, and reconciliation details. Below is a detailed breakdown of its structure, key columns, and functional significance.

Table Overview

The AP_INVOICE_PAYMENTS_ALL table is a multi-organization-enabled table (_ALL suffix), meaning it stores data across operating units while leveraging Oracle's Multi-Org architecture. It serves as a child table to AP_INVOICES_ALL, linking payments to their respective invoices via the INVOICE_ID column. Each record represents a payment or partial payment against an invoice, supporting complex payment scenarios like installments or discounts.

Key Columns and Descriptions

  • INVOICE_PAYMENT_ID: Primary key, uniquely identifying each payment record.
  • INVOICE_ID: Foreign key referencing AP_INVOICES_ALL, linking the payment to its invoice.
  • PAYMENT_NUM: Sequential number for partial payments (e.g., 1 for the first installment).
  • PAYMENT_AMOUNT: The actual amount paid, in the invoice currency.
  • ACCOUNTING_DATE: GL date for the payment, used for financial reporting.
  • PAYMENT_TYPE_FLAG: Indicates payment type (e.g., 'A' for Automatic, 'M' for Manual).
  • PAYMENT_METHOD_CODE: References IBY_PAYMENT_METHODS (e.g., CHECK, WIRE, EFT).
  • PAYMENT_STATUS_FLAG: Tracks payment lifecycle (e.g., 'N' for Negotiable, 'C' for Cleared).
  • BANK_ACCOUNT_NUM: Bank account used for the payment.
  • CHECK_ID: Links to AP_CHECKS_ALL for check-based payments.
  • ORG_ID: Operating unit identifier for Multi-Org segregation.

Functional Significance

  1. Payment Processing: The table records all payment transactions, including manual checks, electronic payments, and refunds. It supports partial payments and prepayments, with PAYMENT_NUM tracking multiple payments per invoice.
  2. Reconciliation: Columns like PAYMENT_STATUS_FLAG and ACCOUNTING_DATE facilitate bank reconciliation and GL period close processes.
  3. Reporting and Auditing: Provides a granular audit trail for payments, enabling compliance and dispute resolution.
  4. Integration: Interfaces with Oracle Cash Management (CE) for bank statement reconciliation and Oracle General Ledger (GL) for accounting entries.

Technical Considerations

  • Indexes: Key indexes include AP_INVOICE_PAYMENTS_N1 (INVOICE_ID) and AP_INVOICE_PAYMENTS_U1 (INVOICE_PAYMENT_ID) for performance optimization.
  • Dependencies: The table is referenced by views like AP_PAYMENT_SCHEDULES_ALL and interfaces with Oracle Payments (IBY) for electronic payment processing.
  • Custom Extensions: Customizations often add columns (e.g., ATTRIBUTE1-15) for client-specific data capture.

Common Use Cases

  • Querying unpaid invoices by filtering on PAYMENT_STATUS_FLAG.
  • Generating payment registers using PAYMENT_METHOD_CODE and ACCOUNTING_DATE.
  • Troubleshooting payment failures via PAYMENT_TYPE_FLAG and error logs.
In summary, AP_INVOICE_PAYMENTS_ALL is a foundational table in Oracle EBS AP, enabling end-to-end payment lifecycle management. Its design supports multi-currency, multi-org, and complex payment scenarios, making it indispensable for financial operations and reporting.