Search Results ap_credit_card_trxns_all




Overview
Explore in AI Chat

Overview

The AP_CREDIT_CARD_TRXNS_ALL table is a core transactional entity within the Oracle E-Business Suite Payables module, specifically for versions 12.1.1 and 12.2.2. It serves as the central repository for detailed information pertaining to employee credit card transactions. Its primary role is to store raw transaction data imported from corporate credit card programs, which is subsequently used for expense report creation, reconciliation, and audit. The table's multi-org structure, indicated by the "_ALL" suffix, allows it to store data for multiple operating units, a critical feature for global implementations. It acts as the foundational link between external credit card feeds and internal expense management and payable processes.

Key Information Stored

The table's primary key is the TRX_ID, a unique identifier for each imported credit card transaction. Key foreign key columns establish critical relationships to other Payables entities. The CARD_ID links to AP_CARDS_ALL, identifying the specific corporate card used. The REPORT_HEADER_ID associates a transaction with an expense report in AP_EXPENSE_REPORT_HEADERS_ALL once it is claimed. The CARD_ACCEPTOR_ID references AP_CC_ACCEPTORS for merchant details, while LOCATION_ID links to AP_EXP_LOCATIONS. Other significant columns include transaction amounts, dates (transaction and posting), merchant names and locations, and a REQUEST_ID linking to FND_CONCURRENT_REQUESTS to track the import process. The table's design captures the complete lifecycle of a card transaction from import through to invoice creation.

Common Use Cases and Queries

A primary use case is reconciling imported card transactions with employee-submitted expense reports. Analysts frequently query for unclaimed transactions to identify outstanding employee expenses. A common reporting pattern involves joining to card and employee tables to analyze spending.

  • Find unclaimed transactions for a specific card:
    SELECT trx.merchant_name, trx.transaction_amount, trx.transaction_date
    FROM ap_credit_card_trxns_all trx
    WHERE trx.card_id = 1001
    AND trx.report_header_id IS NULL;
  • Summarize monthly spending by merchant category:
    SELECT trx.merchant_category, SUM(trx.transaction_amount)
    FROM ap_credit_card_trxns_all trx
    WHERE EXTRACT(YEAR FROM trx.transaction_date) = 2023
    GROUP BY trx.merchant_category;

This table is also central to the "No Match" process, where transactions are identified as potentially invalid or fraudulent via the AP_NO_MATCH_TRXNS table.

Related Objects

As indicated by its foreign key relationships, AP_CREDIT_CARD_TRXNS_ALL is deeply integrated with the Payables schema. It is a parent table to AP_CC_TRX_DETAILS, which holds additional line-level detail. It is referenced as a child table by AP_EXPENSE_REPORT_HEADERS_ALL and AP_EXPENSE_REPORT_LINES_ALL upon expense report creation. Crucially, it is linked to the invoice creation process via AP_INVOICE_LINES_INTERFACE and AP_INVOICE_DISTRIBUTIONS_ALL, where the CREDIT_CARD_TRX_ID column creates the audit trail from card transaction to payable invoice. The table AP_NO_MATCH_TRXNS has a recursive relationship with it to track suspect transactions. For data integrity, key lookups are performed against AP_CARDS_ALL, AP_CC_ACCEPTORS, and AP_EXP_LOCATIONS.