Results for “pre_processed_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

CN_COMMISSION_LINES_ALL is the core transactional fact table within Oracle Incentive Compensation (CN). According to the ETRM documentation, it stores all transactions created as part of the commission calculation process — the output of the Calculate Incentive Compensation concurrent program. Each row represents a single commissionable event, such as a sales order, invoice, or credit memo line, carried through the incentive engine from raw transaction through quota attribution, rate tier assignment, and final commission or payment determination. The table is owned by the CN schema and is valid in EBS 12.1.1 and 12.2.2.

The documented physical schema contains 126 columns, and the table is org-striped through ORG_ID with a unique index CN_COMMISSION_LINES_U1 on (COMMISSION_LINE_ID, ORG_ID), while the primary key constraint CN_COMMISSION_LINES_PK is defined on COMMISSION_LINE_ID alone. From a Data Vault modeling perspective, the mined FK structure suggests a satellite-leaning classification: the table is dense with descriptive and measure attributes (amounts, rates, factors, statuses, dates) attached to a business transaction entity, rather than acting as a pure hub of unique keys or a link resolving many-to-many relationships. The high column count and heavy attribute payload are characteristic of a satellite.

Key Information Stored

• COMMISSION_LINE_ID — surrogate primary key (CN_COMMISSION_LINES_PK); the unique business-key candidate is the composite (COMMISSION_LINE_ID, ORG_ID) via CN_COMMISSION_LINES_U1.
• COMMISSION_HEADER_ID — FK to CN_COMMISSION_HEADERS_ALL; groups lines under a calculation header run.
• TRX_SALES_LINE_ID — FK to CN_TRX_SALES_LINES_ALL; the source transaction line that triggered the calculation.
• CREDITED_SALESREP_ID and DIRECT_SALESREP_ID — the sales representative credited with the sale (FK to CN_SALESREPS_API_ALL) and the direct rep, enabling split and rollup commission logic.
• PROCESSED_PERIOD_ID — FK to CN_PERIOD_STATUSES_ALL; the period in which the line was processed.
• QUOTA_ID, QUOTA_RULE_ID, RATE_TIER_ID, REVENUE_CLASS_ID — the quota, rule, tier, and revenue-classification context applied to the line, each FK-linked to its respective setup table.
• TRANSACTION_AMOUNT, COMMISSIONABLE_AMOUNT, COMMISSION_AMOUNT, QUOTA_AMOUNT — the monetary measures driving the calculation.
• EVENT_FACTOR, PAYMENT_FACTOR, QUOTA_FACTOR, COMMISSION_RATE — the factor and rate values applied to derive payable amounts.
• STATUS, PAYMENT_FLAG, POSTING_STATUS, PENDING_STATUS — lifecycle and accounting state of the line.
• PAYEE_LINE_ID — self-referencing FK supporting rollup and payee hierarchy structures.
• ORIG_CURRENCY_CODE, TRANSACTION_AMOUNT_ORIG, EXCHANGE_RATE — currency and conversion details.
• ORG_ID, SECURITY_GROUP_ID — multi-org and security partition keys.
• ATTRIBUTE1–ATTRIBUTE45 — the standard EBS descriptive flexfield columns for customer-specific extensions.

Common Use Cases and Queries

Because it is the calculation output table, CN_COMMISSION_LINES_ALL is queried extensively for commissions payable reporting, rep earnings statements, quota attainment analysis, and reconciliation of incentive results against source sales transactions. A typical lookup joins the header to obtain the plan run and filters by credited rep and period:

• Query all commissionable lines for a rep in a period: SELECT commission_line_id, transaction_amount, commission_amount, commission_rate FROM cn_commission_lines_all WHERE credited_salesrep_id = :rep_id AND processed_period_id = :period_id AND org_id = :org_id;
• Reconcile to the header run: join CN_COMMISSION_HEADERS_ALL on COMMISSION_HEADER_ID to report totals per calculation run.
• Trace a line back to the source transaction via TRX_SALES_LINE_ID, and forward to postings through CN_POSTING_DETAILS_ALL.COMMISSION_LINE_ID.
• Analyze quota attainment using QUOTA_AMOUNT, QUOTA_ACHIEVED_PTD, and COMMISSION_PAYED_PTD.
• Audit exception and reversal handling via EXCEPTION_CODE, ERROR_CODE, REVERSED_ADJUSTMENT_FLAG, and REVERSAL_HEADER_ID.

Because the volume is high, reports should always constrain by ORG_ID and PROCESSED_PERIOD_ID, and index usage on CREDITED_SALESREP_ID and COMMISSION_HEADER_ID should be verified.

Related Objects

• CN_COMMISSION_HEADERS_ALL — joined on COMMISSION_HEADER_ID; the parent calculation header.
• CN_TRX_SALES_LINES_ALL — joined on TRX_SALES_LINE_ID; source transaction lines.
• CN_SALESREPS_API_ALL — joined on CREDITED_SALESREP_ID and DIRECT_SALESREP_ID; rep master data.
• CN_PERIOD_STATUSES_ALL — joined on PROCESSED_PERIOD_ID; period definitions and status.
• CN_QUOTAS_ALL / CN_QUOTA_RULES_ALL / CN_RATE_TIERS_ALL / CN_REVENUE_CLASSES_ALL — joined on QUOTA_ID, QUOTA_RULE_ID, RATE_TIER_ID, and REVENUE_CLASS_ID; setup context for the calculation.
• CN_POSTING_DETAILS_ALL, CN_PAYMENT_TRANSACTIONS_ALL, CN_LINE_ROLLUP_PATHS_ALL — child tables referencing COMMISSION_LINE_ID for posting, payment, and rollup processing.
• CN_COMMISSION_LINES_ALL (self) — joined on PAYEE_LINE_ID for payee hierarchy and rollup structures.