Search Results cn_trx_all

Overview

The RA_CUSTOMER_TRX_ALL table is the central transactional header table within Oracle E-Business Suite Receivables (AR) for versions 12.1.1 and 12.2.2. It serves as the master repository for all customer-facing transaction documents, including invoices, debit memos, chargebacks, commitments, and credit memos. As a multi-organization enabled table (indicated by the "_ALL" suffix), it stores data for all operating units, making it the primary source for querying and reporting on the complete lifecycle of a transaction from its creation. Its role is fundamental to the AR module's core functions of billing, revenue recognition, and collections.

Key Information Stored

The table's primary key is CUSTOMER_TRX_ID, a unique system-generated identifier for each transaction header. A unique key constraint also exists on the combination of BATCH_SOURCE_ID and TRX_NUMBER, enforcing business-level uniqueness for transaction numbers within a given source. Key columns define the transaction's business context: CUST_TRX_TYPE_ID classifies the document type; BILL_TO_CUSTOMER_ID, SHIP_TO_CUSTOMER_ID, and their related site use and contact IDs define the customer and location; TERM_ID governs payment terms; and PRIMARY_SALESREP_ID captures the responsible party. Critical financial attributes include INVOICE_CURRENCY_CODE, the document currency, and POSTING_CONTROL_ID, which links to the AR_POSTING_CONTROL table for tracking General Ledger transfer status. The table also supports document relationships through columns like PREVIOUS_CUSTOMER_TRX_ID and INITIAL_CUSTOMER_TRX_ID for credit memos and adjustments.

Common Use Cases and Queries

This table is integral for operational reporting, data extracts, and troubleshooting. Common use cases include generating open invoice listings, analyzing transaction volumes by type or customer, and creating audit trails for document sequences. A fundamental query pattern joins to related detail and customer tables:

• Retrieving a basic transaction header: SELECT trx_number, bill_to_customer_id, invoice_currency_code, term_id FROM ra_customer_trx_all WHERE customer_trx_id = :p_trx_id;
• Listing invoices for a customer: SELECT rct.trx_number, rct.creation_date, rcttt.name type_name, rct.invoice_currency_code, rct.term_id FROM ra_customer_trx_all rct, ra_cust_trx_types_all rcttt WHERE rct.bill_to_customer_id = :p_cust_id AND rct.cust_trx_type_id = rcttt.cust_trx_type_id AND rcttt.type = 'INV';
• Analyzing transaction posting status: SELECT posting_control_id, COUNT(*) FROM ra_customer_trx_all WHERE creation_date > SYSDATE - 30 GROUP BY posting_control_id;

Related Objects

RA_CUSTOMER_TRX_ALL has extensive relationships with other core AR and Trading Community Architecture (TCA) objects. The primary child table is RA_CUSTOMER_TRX_LINES_ALL, which stores the line-level details for each header. For customer and site information, it references HZ_CUST_ACCOUNTS, HZ_CUST_SITE_USES_ALL, and HZ_CUST_ACCOUNT_ROLES. Transaction types are defined in RA_CUST_TRX_TYPES_ALL. The table is also referenced by key interfaces and summary tables, such as RA_CUST_TRX_LINE_GL_DIST_ALL for accounting distributions. For programmatic access, the AR_INVOICE_API_PUB package provides public APIs for creating and updating transactions, which internally manipulate this table.