Results for “date_ordered”

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

Overview

CN_TRX_ALL is the core transaction table within the Oracle Incentive Compensation (CN) module. It stores the set of transactions that are captured during the Oracle Receivables data collection process and subsequently made available for compensation plan processing, crediting, and rollup logic. Each row represents a single credited or collected transaction — typically derived from an AR invoice, debit memo, credit memo, or related receivable activity — that has been pulled into the Incentive Compensation staging pipeline for evaluation against compensation plans.

Because the table sits at the center of the collection-to-compensation workflow, it carries identifiers that link back to the originating Receivables transaction, forward to the lines that drive crediting, and upward to the notification and commitment structures used to determine sales credit. The ETRM relationship metadata classifies CN_TRX_ALL as hub-leaning using a heuristic Data Vault model. In practical terms, TRX_ID behaves as a durable business key around which dependent line and API structures attach, making this table a candidate hub in a Data Vault design, with the various transactional details (amounts, dates, flags) representing satellites that would normally be separated for historical tracking.

Key Information Stored

CN_TRX_ALL contains 86 documented columns in the physical schema. The following are the most operationally significant:

• TRX_ID — surrogate primary key, defined by the CN_TRX_PK constraint. This is the value users search for when they reference "cn_trx_pk."
• ORG_ID — operating unit identifier; combined with TRX_ID in the CN_TRX_U1 unique index, which functions as the business-key candidate for the record.
• SOURCE_TRX_ID — foreign key to RA_CUSTOMER_TRX_ALL, identifying the originating AR transaction.
• NOT_TRX_ID — foreign key to CN_NOT_TRX_ALL, linking the transaction to its notification record.
• SOURCE_TRX_NUMBER / SOURCE_ORIG_TRX_NUMBER — human-readable AR references used in reconciliation.
• TRX_TYPE / TRX_TYPE_ID — transaction classification (invoice, credit, debit, etc.); TRX_TYPE_ID joins to IGI_EXP_TRX_TYPE.
• LINE_AMOUNT_APPLIED / INVOICE_TOTAL / PRORATED_AMOUNT — monetary basis for crediting and compensation calculation.
• COLLECTED_DATE / GL_DATE / TRX_DATE — key dates for period and rollup determination.
• STATUS / PROCESSED_PERIOD / COMP_PLAN_PERIOD / ROLLUP_PERIOD — lifecycle state and compensation period assignment.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-tenant data isolation.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — the standard Oracle DFF columns for extensible transaction attributes.

Common Use Cases and Queries

The most frequent reason developers and analysts query this table is to resolve a "cn_trx_pk" reference — locating the transaction row behind a primary key value obtained from a log, an API error, or a downstream interface record.

• Transaction lookup by primary key: SELECT * FROM cn.cn_trx_all WHERE trx_id = :p_trx_id AND org_id = :p_org_id;
• Trace back to the source AR invoice: join SOURCE_TRX_ID to RA_CUSTOMER_TRX_ALL to compare compensation amounts against booked receivables.
• Compensation period reporting: aggregate AMOUNT_APPLIED by PROCESSED_PERIOD or COMP_PLAN_PERIOD and ORG_ID to reconcile crediting results.
• Exception investigation: filter on EXCEPTION_FLAG, NO_REV_ACCT_EXCEPTION, NOT_100_PERCENT_EXCEPTION, or BOTH_NON_ZERO_REV_ACCTS to isolate transactions excluded from compensation.
• Notification reconciliation: join NOT_TRX_ID to CN_NOT_TRX_ALL to confirm which transactions have been notified for processing.

Related Objects

• RA_CUSTOMER_TRX_ALL — joined via CN_TRX_ALL.SOURCE_TRX_ID; the AR source transaction.
• CN_NOT_TRX_ALL — joined via CN_TRX_ALL.NOT_TRX_ID; notification records.
• CN_TRX_LINES_ALL — child table; CN_TRX_LINES_ALL.TRX_ID references CN_TRX_ALL.TRX_ID.
• CN_TRX_SALES_LINES_ALL — child table; references CN_TRX_ALL.TRX_ID for sales credit lines.
• CN_COMM_LINES_API_ALL — API-oriented child table; CN_COMM_LINES_API_ALL.TRX_ID references CN_TRX_ALL.TRX_ID.
• IGI_EXP_TRX_TYPE — referenced by TRX_TYPE_ID for transaction type validation.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for data security.