Results for “cn_commission_headers_all_pk”

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

Overview

CN_COMMISSION_HEADERS_ALL is the transactional header table within the Oracle Incentive Compensation (CN) module. It stores the direct credit transactions attributed to a salesperson, forming the top of the commission processing hierarchy. Each row captures a single commissionable event — a booking, invoice, order, or adjustment — along with the revenue class, quota, plan assignment, and batch context that drive crediting and rollup into commission lines and posting detail records. The table resides in the CN schema and is present and VALID in both Oracle EBS 12.1.1 and 12.2.2. The documented physical schema for 12.2.2 lists 173 columns, reflecting the wide, extensible design typical of Incentive Compensation staging and rollup structures.

From a dimensional modeling perspective, the mined foreign-key topology suggests that CN_COMMISSION_HEADERS_ALL is best classified as a hub-leaning entity. It carries a stable surrogate identity plus numerous business identifiers, while the measurable amounts and the bulk of descriptive attributes could alternatively be modeled as satellite data. This classification is a heuristic derived from FK structure and should be treated as a modeling suggestion rather than an architectural mandate.

Key Information Stored

The surrogate primary key is COMMISSION_HEADER_ID, enforced by the constraint CN_COMMISSION_HEADERS_ALL_PK. A separate unique index, CN_COMMISSION_HEADERS_U1, exists over (COMMISSION_HEADER_ID, ORG_ID), making that pair the documented business-key candidate and confirming the table is partitioned or filtered by operating unit.

• DIRECT_SALESREP_ID — the salesperson credited with the transaction.
• TRANSACTION_AMOUNT and TRANSACTION_AMOUNT_ORIG — the credited amount in functional and original currency; ORIG_CURRENCY_CODE and EXCHANGE_RATE support conversion.
• COMMISSION_AMOUNT — the computed commission earned on the credit.
• QUANTITY, UOM_CODE, DISCOUNT_PERCENTAGE, and MARGIN_PERCENTAGE — transaction measures used in crediting rules.
• REVENUE_CLASS_ID, QUOTA_ID, and SRP_PLAN_ASSIGN_ID — the crediting and plan context for the transaction.
• COMM_LINES_API_ID and TRX_BATCH_ID — linkage to the collection/import batch and line API staging.
• STATUS, PRE_PROCESSED_CODE, and PROCESSED_DATE, PROCESSED_PERIOD_ID — lifecycle and period information.
• REVERSAL_FLAG, REVERSAL_HEADER_ID, NEGATED_FLAG, REASON_CODE — reversal and adjustment tracking.
• Source identifiers such as SOURCE_TRX_ID, SOURCE_TRX_NUMBER, SOURCE_DOC_TYPE, ORDER_NUMBER, and INVOICE_NUMBER.
• ORG_ID, SECURITY_GROUP_ID, and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE).

In addition, more than 100 ATTRIBUTE1–ATTRIBUTE100 DFF columns provide extensibility for customer-specific crediting data.

Common Use Cases and Queries

Typical reporting scenarios include commission credit summaries by salesperson, period, or revenue class; reconciliation of credits against source transactions; and reversal/adjustment analysis. A representative query joining to lines and posting details:

• Credit totals per salesperson: SELECT DIRECT_SALESREP_ID, SUM(COMMISSION_AMOUNT) FROM CN_COMMISSION_HEADERS_ALL WHERE ORG_ID = :org AND PROCESSED_PERIOD_ID = :period GROUP BY DIRECT_SALESREP_ID;
• Reversal inspection: filter on REVERSAL_FLAG = 'Y' and join REVERSAL_HEADER_ID back to COMMISSION_HEADER_ID.
• Source traceability: join to IGI_DOS_TRX_SOURCES via SOURCE_TRX_ID, and to CN_REVENUE_CLASSES_ALL via REVENUE_CLASS_ID.
• Batch audit: group by TRX_BATCH_ID to verify imported credit volumes.

Related Objects

• CN_COMMISSION_LINES_ALL — references this table via COMMISSION_HEADER_ID; holds line-level splits and payee detail.
• CN_POSTING_DETAILS_ALL — joins on COMMISSION_HEADER_ID; carries posting/GL detail.
• CN_PAYMENT_TRANSACTIONS_ALL — references COMMISSION_HEADER_ID for payment linkage.
• CN_LINE_ROLLUP_PATHS_ALL — references COMMISSION_HEADER_ID for rollup hierarchy.
• CN_REVENUE_CLASSES_ALL — joined via REVENUE_CLASS_ID.
• CN_QUOTAS_ALL and CN_SRP_PLAN_ASSIGNS_ALL — joined via QUOTA_ID and SRP_PLAN_ASSIGN_ID.
• CN_TRX_BATCHES_ALL and CN_COMM_LINES_API_ALL — joined via TRX_BATCH_ID and COMM_LINES_API_ID.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for data security.