Search Results ap_history_inv_payments_all




Overview
Explore in AI Chat

Overview

The AP_HISTORY_INV_PAYMENTS_ALL table is a critical data retention object within the Oracle E-Business Suite Payables (AP) module. Its primary function is to preserve the relationship between purged invoice headers and their associated payment records after a data archival or purge operation is executed. In standard Payables processing, active invoice and payment relationships are managed in transactional tables. However, to maintain database performance and manage data volume, organizations periodically purge historical transaction data. This table ensures the referential integrity and audit trail of payment history is not lost when the primary invoice records are removed from the active tables. It serves as a cornerstone for compliance and historical reporting on financial obligations that have been settled.

Key Information Stored

The table acts as a junction, storing key identifiers that link purged data. While the full column list is not detailed in the provided metadata, its described purpose and foreign key relationships define its core structure. The essential columns include a unique identifier for the table's record, and the foreign key columns INVOICE_ID and CHECK_ID. The INVOICE_ID references the purged invoice's unique identifier now stored in AP_HISTORY_INVOICES_ALL. The CHECK_ID references the associated payment record, typically a check or electronic payment, now stored in AP_HISTORY_CHECKS_ALL. The table likely also contains audit columns such as CREATION_DATE and LAST_UPDATE_DATE to track when the historical relationship record was created, often during the purge process itself.

Common Use Cases and Queries

The primary use case is historical inquiry and reconciliation after data purges. Auditors or financial analysts may need to verify that a specific purged invoice was paid, or identify all payments made against a purged supplier invoice. A typical query would join this history table to the corresponding history tables for invoices and checks.

  • Sample Query: To retrieve payment details for a purged invoice, one might use: SELECT hip.*, hi.invoice_num, hc.check_number FROM ap_history_inv_payments_all hip JOIN ap_history_invoices_all hi ON hip.invoice_id = hi.invoice_id JOIN ap_history_checks_all hc ON hip.check_id = hc.check_id WHERE hi.invoice_num = 'INV12345';
  • Reporting: Generating reports on total payments made to a supplier within a purged date range, requiring joins from history invoices (for supplier) to history payments to history checks.
  • Data Validation: Ensuring the purge process completed correctly by verifying that for every purged paid invoice, a corresponding record exists in AP_HISTORY_INV_PAYMENTS_ALL.

Related Objects

This table is central to the Payables historical data model and has defined foreign key relationships with two other key history tables, as per the provided metadata:

  • AP_HISTORY_INVOICES_ALL: Linked via the AP_HISTORY_INV_PAYMENTS_ALL.INVOICE_ID column. This table stores the header information for purged invoices.
  • AP_HISTORY_CHECKS_ALL: Linked via the AP_HISTORY_INV_PAYMENTS_ALL.CHECK_ID column. This table stores the payment details for purged checks and potentially other payment documents.

These relationships form the backbone for reconstructing the complete financial transaction lifecycle (invoice -> payment) for data that no longer resides in the primary transactional tables like AP_INVOICES_ALL and AP_CHECKS_ALL.