Search Results commission_line_id

Overview

The CN_COMMISSION_LINES_ALL table is a core transactional data store within the Oracle E-Business Suite Incentive Compensation (CN) module. It functions as the detailed ledger for all calculated commission transactions. When the Incentive Compensation engine processes earnings, such as sales orders or adjustments, it generates commission headers and corresponding line-level details. This table is the definitive repository for those line-level details, capturing the granular financial outcomes of compensation plans for each credited sales representative. Its role is critical for audit trails, payment processing, dispute resolution, and all commission-related reporting, forming the backbone of the calculated commission data model.

Key Information Stored

While the full column list is extensive, the table's structure is defined by key identifiers and financial attributes. The primary key, COMMISSION_LINE_ID, uniquely identifies each transaction line. Essential foreign key columns link the line to its parent header (COMMISSION_HEADER_ID), the credited salesperson (CREDITED_SALESREP_ID), and the accounting period (PROCESSED_PERIOD_ID). Crucially, it stores references to the compensation plan components that drove the calculation, namely the QUOTA_ID and QUOTA_RULE_ID. Financial amounts, such as the calculated commission value and the transaction quantity or revenue upon which it is based, are stored here. The PAYEE_LINE_ID column supports complex scenarios like splits or overrides by creating a hierarchical relationship between payer and payee lines within the same table.

Common Use Cases and Queries

This table is central to operational and analytical queries. A common use case is generating a detailed commission statement for a sales representative, joining to the headers and salesrep tables. Analysts frequently query it to analyze payout patterns by product, period, or plan rule. Support teams use it to trace the calculation lineage of a specific payment. A foundational query pattern involves joining to related dimension tables to make data human-readable.

• Sample Query Pattern:
  SELECT cl.commission_line_id,
        sr.salesrep_name,
        ch.transaction_number,
        cl.calculated_commission_amt,
        ps.period_name
  FROM cn_commission_lines_all cl,
       cn_commission_headers_all ch,
       cn_salesreps_api_all sr,
       cn_period_statuses_all ps
  WHERE cl.commission_header_id = ch.commission_header_id
  AND cl.credited_salesrep_id = sr.salesrep_id
  AND cl.processed_period_id = ps.period_status_id
  AND ps.period_name = 'JAN-2024';

Related Objects

The CN_COMMISSION_LINES_ALL table has defined foreign key relationships with several key tables in the Incentive Compensation schema, as documented in the ETRM metadata. These relationships are essential for accurate data retrieval and integrity.

• CN_COMMISSION_HEADERS_ALL: Links via COMMISSION_HEADER_ID. Every commission line must belong to a parent transaction header.
• CN_SALESREPS_API_ALL: Links via CREDITED_SALESREP_ID. Identifies the sales representative who earns the commission.
• CN_PERIOD_STATUSES_ALL: Links via PROCESSED_PERIOD_ID. Associates the line with the accounting period in which it was processed.
• CN_QUOTAS_ALL: Links via QUOTA_ID. References the quota assignment that may have influenced the calculation.
• CN_QUOTA_RULES_ALL: Links via QUOTA_RULE_ID. References the specific rate or rule applied from the compensation plan.
• CN_RATE_TIERS_ALL: Links via RATE_TIER_ID. Points to the specific tier rate used if the plan included a tiered structure.
• Self-Referential (CN_COMMISSION_LINES_ALL): Links via PAYEE_LINE_ID. Creates parent-child relationships between lines for handling payment splits, overrides, or cascading calculations.