Search Results card_history_change_id

Overview

The IBY_CREDITCARD_H table is a core data object within the Oracle E-Business Suite (EBS) Payments module (IBY). It functions as a history table, systematically tracking all changes made to credit card instrument records stored in the primary IBY_CREDITCARD table. Its role is critical for maintaining a complete audit trail of credit card data modifications, which is essential for compliance, dispute resolution, and historical analysis of payment instrument details. Every time a credit card record is created, updated, or its status is altered, a corresponding historical entry is logged in this table.

Key Information Stored

The table stores a snapshot of the credit card instrument's state at the point of each change. Its primary key, CARD_HISTORY_CHANGE_ID, uniquely identifies each historical event. The most critical foreign key column is INSTRID, which links each history record back to its parent instrument in the IBY_CREDITCARD table. Other significant foreign keys include CARD_ISSUER_CODE (linking to IBY_CREDITCARD_ISSUERS_B for issuer details), CARD_OWNER_ID (linking to HZ_PARTIES for the owning customer), and ADDRESSID (linking to HZ_PARTY_SITE_USES for the associated address). The table typically contains columns mirroring those in IBY_CREDITCARD, such as card number (often masked), expiration date, card type, and status, along with metadata like the date of the change and the user or process that initiated it.

Common Use Cases and Queries

This table is primarily used for auditing and reporting on the lifecycle of a credit card instrument. Common scenarios include tracing the modification history of a specific card to investigate data integrity issues, generating compliance reports showing all cards that were deactivated within a certain period, or analyzing patterns in card updates. A typical query would join IBY_CREDITCARD_H with IBY_CREDITCARD and customer tables to produce a chronological audit report for a given instrument.

SELECT c.instrument_payment_id,
       h.card_number_masked,
       h.expiration_date,
       h.status,
       h.last_update_date
FROM iby_creditcard c,
     iby_creditcard_h h
WHERE c.instrument_id = h.instrid
AND c.instrument_payment_id = :p_card_ref
ORDER BY h.last_update_date DESC;

Related Objects

• IBY_CREDITCARD: The primary parent table. IBY_CREDITCARD_H.INSTRID is a foreign key to IBY_CREDITCARD.INSTRUMENT_ID.
• IBY_CREDITCARD_ISSUERS_B: Provides issuer details via the foreign key on CARD_ISSUER_CODE.
• HZ_PARTIES: Stores party information for the card owner via the CARD_OWNER_ID foreign key.
• HZ_PARTY_SITE_USES: Stores address usage details via the ADDRESSID foreign key.