Search Results payment_history_id

Overview

The IBY_PAYMENTS_H table is a core data object within the Oracle E-Business Suite Payments (IBY) module, specifically designed for audit and historical tracking. It serves as a historical snapshot repository for payment records. Unlike transactional tables that hold the current state of a payment, this table captures denormalized versions of payments at specific, significant points in their lifecycle, such as when a payment is submitted to an external payment system. This design allows the system to preserve an immutable record of what a payment looked like at a given historical moment, enabling detailed audit trails, compliance reporting, and analysis of payment state changes over time.

Key Information Stored

The table's primary key is PAYMENT_HISTORY_ID, which uniquely identifies each historical snapshot. The data stored is a comprehensive denormalized record of a payment. Based on its foreign key relationships, critical columns include PAYMENT_INSTRUCTION_ID (linking to the source IBY_PAY_INSTRUCTIONS_ALL), PAYMENT_SERVICE_REQUEST_ID, and PAYMENT_INSTRUCTION_HISTORY_ID. It also stores payment-specific attributes like BANK_INSTRUCTION1_CODE, BANK_INSTRUCTION2_CODE, PAYMENT_REASON_CODE, and COUNTRY. The denormalized structure means each row contains a complete set of payment data as it existed at the snapshot time, facilitating efficient historical queries without complex joins to multiple transactional tables.

Common Use Cases and Queries

The primary use case is auditing the complete state of a payment at the moment it was processed or transmitted. This is crucial for regulatory compliance and dispute resolution. Common reporting scenarios include tracing the evolution of a specific payment or generating reports on all payments submitted to a banking partner on a given date. A typical query pattern involves joining to the payment instructions table to filter on original payment criteria before analyzing the historical snapshot.

SELECT iph.payment_history_id,
       iph.payment_instruction_id,
       iph.payment_status,
       iph.payment_amount,
       iph.payment_date
FROM iby_payments_h iph,
     iby_pay_instructions_all ipi
WHERE iph.payment_instruction_id = ipi.payment_instruction_id
  AND ipi.internal_bank_account_id = 12345
  AND TRUNC(iph.creation_date) = :p_snapshot_date;

Another key use is investigating the lineage between a payment instruction and its related documents, often by joining to IBY_DOCUMENTS_PAYABLE_H.

Related Objects

IBY_PAYMENTS_H is centrally connected to several key Payments tables. Its main foreign key relationships are:

• IBY_PAY_INSTRUCTIONS_ALL: The source transactional payment instruction.
• IBY_PAY_INSTRUCTIONS_H: The historical snapshot of the related payment instruction.
• IBY_PAY_SERVICE_REQUESTS: The overarching payment process request.
• IBY_DOCUMENTS_PAYABLE_H: A critical relationship where historical documents (invoices) reference the payment snapshot via PAYMENT_HISTORY_ID.
• IBY_BANK_INSTRUCTIONS_B & IBY_PAYMENT_REASONS_B: Reference data for bank instructions and payment reasons.
• FND_TERRITORIES: Reference data for the payment country.