Search Results cn_trx_all




Overview
Explore in AI Chat

Overview

The CN_TRX_ALL table is a core transactional entity within the Oracle E-Business Suite Incentive Compensation (CN) module. It serves as the primary staging and storage point for all transaction data collected from external source systems, most notably from the Receivables (AR) module via the AR Data Collection process. This table is fundamental to the commission calculation engine, as it holds the raw transaction details—such as sales orders and invoices—that are later assessed against compensation plans to determine earnings for sales representatives. Its role is to act as the system of record for all compensable events before they are processed into commissionable lines.

Key Information Stored

While the provided metadata does not list specific columns, the primary and foreign key relationships define its critical structure. The table is uniquely identified by the TRX_ID column, which is the primary key (CN_TRX_PK). Two essential foreign key columns establish links to source data: SOURCE_TRX_ID references the RA_CUSTOMER_TRX_ALL table, directly tying each incentive transaction to its originating AR invoice. The NOT_TRX_ID column references the CN_NOT_TRX_ALL table, which is used to manage transactions that are marked as non-commissionable. Typical data stored includes transaction dates, amounts, currency, customer information, and identifiers linking back to the original sales order or invoice.

Common Use Cases and Queries

This table is central to data validation, reconciliation, and commission reporting. A common use case involves reconciling collected transactions with source AR invoices to ensure data integrity before plan processing. Analysts frequently query this table to investigate transaction volumes, identify transactions pending processing, or diagnose issues with the data collection. A typical query pattern joins CN_TRX_ALL to source and related compensation tables:

  • Transaction Reconciliation: SELECT cta.trx_id, cta.source_trx_id, rcta.trx_number FROM cn_trx_all cta, ra_customer_trx_all rcta WHERE cta.source_trx_id = rcta.customer_trx_id AND cta.creation_date > SYSDATE - 30;
  • Identifying Unprocessed Transactions: Queries often join CN_TRX_ALL to CN_TRX_LINES_ALL or CN_COMM_LINES_API_ALL to find transactions that have been collected but not yet fully processed into commission lines.

Related Objects

The CN_TRX_ALL table sits at the center of a key data model, with documented relationships to several other CN tables.

  • Referenced By This Table (Outgoing Foreign Keys):
    • CN_NOT_TRX_ALL: Joined via CN_TRX_ALL.NOT_TRX_ID. Links a transaction to its non-commissionable reason.
    • RA_CUSTOMER_TRX_ALL: Joined via CN_TRX_ALL.SOURCE_TRX_ID. Links to the source AR invoice.
  • References This Table (Incoming Foreign Keys):
    • CN_COMM_LINES_API_ALL: References via TRX_ID. Stores the final calculated commission lines derived from the transaction.
    • CN_TRX_LINES_ALL: References via TRX_ID. Holds detailed line-level breakdowns of the transaction.
    • CN_TRX_SALES_LINES_ALL: References via TRX_ID. Stores sales team assignments and credit splits for the transaction.