Search Results cn_payment_transactions_u1

Overview

CN.CN_PAYMENT_TRANSACTIONS_ALL is the core transactional table used by the Oracle EBS Incentive Compensation (CN) payment module to store the details of every payment worksheet line generated during payrun processing. Each row represents a single posting amount attributable to a payee, a plan element, and an incentive event within a specific pay period. The table acts as the staging and audit foundation for transferring calculated compensation into Oracle Payables and General Ledger via the posting batch mechanism.

Population granularity is profile-driven. When the Pay by Transaction profile option is set to Yes, records are inserted at the same granularity at which they were calculated (transaction level). When the profile is set to No, records are consolidated at the plan element level. This behavioral distinction makes the table central to reconciling worksheet output against downstream payment and accounting entries.

From a modeling perspective, the heuristic Data Vault classification for this object is link. It does not describe a single business entity in isolation; instead it associates multiple hubs — payee salesrep, commission header/line, quota, rate tier, payrun, and posting batch — together with the transactional measures (amount, commission rate, payment amount) and descriptive attributes (flags, dates, flex attributes) surrounding that association. Dimensional modelers would likely treat it as a fact table with degenerate and foreign-key dimensions rather than a standalone reference table.

Key Information Stored

The table contains 58 documented columns. The surrogate primary key is PAYMENT_TRANSACTION_ID, which is enforced together with ORG_ID through the unique index CN_PAYMENT_TRANSACTIONS_U1. This composite is the documented business-key candidate and also serves as the multi-org partitioning key.

The most significant columns for functional and reporting purposes are:

• PAYMENT_TRANSACTION_ID — surrogate row identifier; first column of the unique key.
• ORG_ID — operating unit identifier; second column of the unique key and the primary multi-org discriminator.
• PAYRUN_ID — foreign key to CN_PAYRUNS_ALL, tying the record to a specific payrun execution.
• COMMISSION_HEADER_ID and COMMISSION_LINE_ID — foreign keys to the commission header and commission line, giving the originating calculation context.
• CREDITED_SALESREP_ID — the salesrep receiving credit for the transaction; a leading column in several non-unique indexes.
• PAYEE_SALESREP_ID and ROLE_ID — payee identity and the role under which the payment is made.
• INCENTIVE_TYPE_CODE — Commission, Bonus, or Manual; used in index CN_PAYMENT_TRANSACTIONS_N1 and N8.
• AMOUNT — the amount to be posted for the line; the primary monetary measure.
• PAYMENT_AMOUNT — the net payment amount after adjustments, holds, and waivers.
• COMMISSION_RATE, EVENT_FACTOR, PAYMENT_FACTOR, QUOTA_FACTOR, INPUT_ACHIEVED — calculation inputs retained for audit and reversal.
• QUOTA_ID, QUOTA_RULE_ID, RATE_TIER_ID, SRP_PLAN_ASSIGN_ID — foreign keys to the plan constructs that drove the calculation.
• PAY_PERIOD_ID and PROCESSED_PERIOD_ID — pay period and the GL period in which the transaction was processed.
• POSTING_BATCH_ID and POSTING_TYPE — link to the posting batch and classification of the posting transaction.
• HOLD_FLAG, PAID_FLAG, WAIVE_FLAG, RECOVERABLE_FLAG — status and adjustment indicators; RECOVERABLE_FLAG distinguishes recoverable from non-recoverable manual pay adjustments (Y/N).
• LIABILITY_CCID and EXPENSE_CCID — GL code combinations for the liability and expense postings.

Common Use Cases and Queries

The most frequent use of this table is payment reconciliation: matching worksheet output to payrun totals and to posted GL entries. A typical query aggregates posting amounts by payrun and salesrep:

• SELECT payrun_id, credited_salesrep_id, SUM(amount) FROM cn_payment_transactions_all WHERE org_id = :org_id GROUP BY payrun_id, credited_salesrep_id;
• Identifying unreleased worksheet lines: SELECT payment_transaction_id, amount FROM cn_payment_transactions_all WHERE posting_batch_id IS NULL AND org_id = :org_id;
• Checking hold and waiver status: filter on HOLD_FLAG = 'Y' or WAIVE_FLAG = 'Y' to isolate lines withheld from payment.
• Auditing recoverable manual adjustments: WHERE recoverable_flag = 'Y' AND incentive_type_code = 'MANUAL'.
• Tracing an exception back through the calculation chain using commission_header_id, commission_line_id, and quota_id.

The indexes N1, N8, and N9 are designed for these access paths: N1 and N8 cover the payrun/credited salesrep/incentive type/quota combination, N9 supports drill-down from commission line, N10 supports posting batch reporting, and N7 supports pay period range queries.

Related Objects

The documented foreign keys define the principal relationships:

• CN.CN_PAYRUNS_ALL via PAYRUN_ID — the payrun that generated the transactions.
• CN.CN_COMMISSION_HEADERS_ALL via COMMISSION_HEADER_ID — commission calculation header.
• CN.CN_COMMISSION_LINES_ALL via COMMISSION_LINE_ID — commission calculation detail line.
• CN.CN_POSTING_BATCHES_ALL via POSTING_BATCH_ID — the posting batch used to transfer amounts to Payables/GL.
• CN.CN_SRP_PLAN_ASSIGNS_ALL via SRP_PLAN_ASSIGN_ID — the salesrep plan assignment in force.
• CN.CN_QUOTAS_ALL via QUOTA_ID and CN.CN_QUOTA_RULES_ALL via QUOTA_RULE_ID — quota and quota rule context.
• CN.CN_RATE_TIERS_ALL via RATE_TIER_ID — the rate tier applied to the calculation.
• IGS_FI_CR_TYPES_ALL via CREDIT_TYPE_ID — credit type classification.
• FND_SECURITY_GROUPS via SECURITY_GROUP_ID — security group used for row-level access control.

Because the table carries ORG_ID and is registered in FND Design Data as CN.CN_PAYMENT_TRANSACTIONS_ALL, queries should always be constrained by operating unit. Concurrent programs in the Incentive Compensation payment flow read and write this table directly, and no public PL/SQL API is documented for direct DML; changes should be made through the standard payrun and posting processes.