Search Results cn_trx_sales_lines_all




Overview
Explore in AI Chat

Overview

The CN_TRX_SALES_LINES_ALL table is a core transactional data store within the Oracle E-Business Suite Incentive Compensation (CN) module. It functions as the central repository for sales transaction line details captured during the data collection process from Oracle Receivables (AR). This table is integral to the commission calculation engine, as it holds the granular line-level revenue data against which compensation plans, rules, and rates are applied. Its role is to provide the detailed, auditable source of sales credit that feeds the subsequent commission calculation and payout workflows. As an "ALL" table, it is designed to support multi-organization access (MOAC) by storing data across all operating units.

Key Information Stored

While the provided metadata does not list specific columns, the table's primary key and foreign key relationships define its essential structure and the nature of the data it contains. The primary key, TRX_SALES_LINE_ID, uniquely identifies each sales line record. The foreign key column TRX_ID links each line to its parent transaction header in the CN_TRX_ALL table, which contains high-level information like transaction date and customer. The foreign key column TRX_LINE_ID links to the CN_TRX_LINES_ALL table, which holds more detailed transaction line information. Typical data stored in this table would include the credited salesperson, the revenue amount eligible for commission, the product or service sold, and the associated quota categories or performance measures used in incentive plans.

Common Use Cases and Queries

The primary use case for this table is reporting and troubleshooting within the incentive compensation lifecycle. Analysts and administrators query it to verify that sales data has been correctly collected from source systems and to audit the data before running commission calculations. A common query pattern involves joining to its parent tables to get a complete view of a transaction for reconciliation purposes.

  • Sample Query for Transaction Audit: SELECT ctsla.trx_sales_line_id, cta.trx_number, ctla.line_number, ctsla.credited_salesrep_id, ctsla.revenue_amount FROM cn_trx_sales_lines_all ctsla JOIN cn_trx_all cta ON ctsla.trx_id = cta.trx_id JOIN cn_trx_lines_all ctla ON ctsla.trx_line_id = ctla.trx_line_id WHERE cta.trx_date BETWEEN :p_start_date AND :p_end_date;
  • Data Validation: Queries are frequently executed to ensure data integrity, such as checking for sales lines that are not properly linked to a parent transaction or that have null critical fields like revenue amount.

Related Objects

The CN_TRX_SALES_LINES_ALL table sits at the center of key relationships within the Incentive Compensation schema, linking source transactions to calculated commissions.

  • Referenced Tables (Outgoing Foreign Keys):
    • CN_TRX_ALL: Linked via CN_TRX_SALES_LINES_ALL.TRX_ID. This provides header-level context for the sales line.
    • CN_TRX_LINES_ALL: Linked via CN_TRX_SALES_LINES_ALL.TRX_LINE_ID. This provides additional transaction line details.
  • Referencing Table (Incoming Foreign Key):
    • CN_COMM_LINES_API_ALL: Linked via CN_COMM_LINES_API_ALL.TRX_SALES_LINE_ID. This is a critical relationship, as it shows which calculated commission lines in the API table were generated from this specific source sales transaction line.