Search Results ra_customer_trx_all




Overview
Explore in AI Chat

Overview

RA_CUSTOMER_TRX_ALL is the header-level transaction table in the Oracle Receivables (AR) module of Oracle E-Business Suite, owned by the AR schema. It stores the header information for every customer transaction generated within Receivables, including invoices, debit memos, chargebacks, commitments, and credit memos. Each row represents a single transaction document identified by a system-generated transaction number and links to customer, site, terms, and legal entity data required for billing and collections processing.

From a Data Vault modeling perspective, the heuristic classification for this table is hub, reflecting its role as a central entity anchoring transaction identities. Line-level and distribution-level detail is stored in subordinate tables such as RA_CUSTOMER_TRX_LINES_ALL and RA_CUST_TRX_LINE_GL_DIST_ALL, while accounting and payment information is captured in AR_PAYMENT_SCHEDULES_ALL and AR_RECEIVABLE_APPLICATIONS_ALL. The table is documented with 187 columns in the 12.2.2 ETRM schema and is marked VALID, making it a critical object for AR reporting, reconciliation, and integration.

Key Information Stored

The primary surrogate key is CUSTOMER_TRX_ID, enforced by RA_CUSTOMER_TRX_PK. Several business-key candidates are also documented through unique indexes: RA_CUSTOMER_TRX_U1 (CUSTOMER_TRX_ID), RA_CUSTOMER_TRX_U2 (REVERSED_CASH_RECEIPT_ID), and RA_CUSTOMER_TRX_U3 (DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE), the latter supporting document sequencing for legal numbering. A composite unique constraint RA_CUSTOMER_TRX_UK1 spans BATCH_SOURCE_ID and TRX_NUMBER.

The most significant columns include:

Common Use Cases and Queries

Typical reporting scenarios include open receivables aging, invoice and credit memo reconciliation, tax and revenue analysis, and document sequencing audits. A common query joins the header to customer data and payment schedules:

SELECT rct.trx_number, rct.trx_date, rct.invoice_currency_code,
       hca.account_number, aps.amount_due_remaining
FROM   ra_customer_trx_all rct,
       hz_cust_accounts hca,
       ar_payment_schedules_all aps
WHERE  rct.bill_to_customer_id = hca.cust_account_id
AND    rct.customer_trx_id = aps.customer_trx_id
AND    rct.set_of_books_id = :ledger_id
AND    rct.trx_date BETWEEN :from_date AND :to_date;

Self-joins on PREVIOUS_CUSTOMER_TRX_ID or INITIAL_CUSTOMER_TRX_ID are used to trace credit memo chains against originating invoices. Filtering by COMPLETE_FLAG = 'Y' restricts results to completed transactions, and ORG_ID is a standard multi-org predicate when running in a multi-org environment. Integration use cases typically extract transaction headers via the AR AutoInvoice interface or query this table alongside RA_CUSTOMER_TRX_LINES_ALL to feed downstream analytics and data warehouses.

Related Objects

The table is central to the AR data model, with numerous inbound and outbound foreign-key relationships:

  • RA_CUSTOMER_TRX_LINES_ALL — child table holding line-level invoice detail, joined via CUSTOMER_TRX_ID.
  • AR_PAYMENT_SCHEDULES_ALL — captures due dates and payment schedules per transaction.
  • AR_RECEIVABLE_APPLICATIONS_ALL — records application of receipts and credit memos, referenced by CUSTOMER_TRX_ID and APPLIED_CUSTOMER_TRX_ID.
  • AR_ADJUSTMENTS_ALL — stores adjustments applied against transactions through CUSTOMER_TRX_ID.
  • RA_CUST_TRX_LINE_GL_DIST_ALL — accounting distributions tied to the transaction header.
  • HZ_CUST_ACCOUNTS — the billing, ship-to, sold-to, and paying customer references.
  • HZ_CUST_SITE_USES_ALL — site-use references for billing, shipping, and remit-to locations.
  • RA_CUST_TRX_TYPES_ALL — defines transaction type attributes such as class and automatic numbering.
  • RA_TERMS_B — payment terms applied to the transaction.
  • RA_CUSTOMER_TRX_ALL (self-reference) — via PREVIOUS_CUSTOMER_TRX_ID and INITIAL_CUSTOMER_TRX_ID.

Other significant references include OKC_K_HEADERS_B (CONTRACT_ID), SO_AGREEMENTS_B (AGREEMENT_ID), FV_LEGAL_ENTITIES (LEGAL_ENTITY_ID), and FND_DOCUMENT_SEQUENCES (DOC_SEQUENCE_ID). The breadth of these relationships confirms the table's position as a core AR hub.