Search Results cn_payment_transactions_all




Overview
Explore in AI Chat

Overview

CN_PAYMENT_TRANSACTIONS_ALL is a transaction-level table owned by the CN schema within the Oracle Incentive Compensation (CN) module. It stores the individual payment transaction records that make up a payment worksheet, capturing how calculated commission, bonus, and incentive amounts resolve into payable line items. The set of records created here is governed by the profile option Pay by Transaction: when this profile is set to Y(es), payment processing operates at the transaction grain, and each qualifying transaction generates a row in this table. When disabled, aggregation occurs and fewer discrete rows are written.

From a heuristic Data Vault modeling perspective, this object is classified as standalone, meaning it does not contain classic hub-to-link dependencies mined from its own primary key structure. In practice it behaves as a satellite-like transaction fact table: it carries descriptive attributes and measurable amounts, plus multiple foreign keys that reference surrounding master and transactional entities. The documented physical schema for 12.2.2 comprises 58 columns, and the table exposes one unique business-key candidate, CN_PAYMENT_TRANSACTIONS_U1 on (PAYMENT_TRANSACTION_ID, ORG_ID), which confirms multi-org partitioning via ORG_ID.

Key Information Stored

The surrogate primary key is PAYMENT_TRANSACTION_ID, uniquely identifying each payment transaction line. Because ORG_ID participates in the unique index, the same identifier is scoped to a single operating unit. The most operationally significant columns include:

Standard Who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and an OBJECT_VERSION_NUMBER for optimistic locking are also present, along with fifteen ATTRIBUTE flex columns and ATTRIBUTE_CATEGORY.

Common Use Cases and Queries

Typical scenarios include auditing pay runs, reconciling payable amounts to commission calculations, and diagnosing held or waived transactions. A common pattern joins the payment transaction to its pay run and payee:

  • Pay run reconciliation — total PAYMENT_AMOUNT grouped by PAYRUN_ID, restricted to PAID_FLAG = 'Y'.
  • Hold/waive analysis — filtering on HOLD_FLAG = 'Y' or WAIVE_FLAG = 'Y' to explain unpaid balances.
  • Salesrep earnings reporting — aggregating AMOUNT by PAYEE_SALESREP_ID and PAY_PERIOD_ID.
  • Traceability — tracing a payment back through COMMISSION_LINE_ID to the source commission line.
  • Posting verification — checking that rows with a populated POSTING_BATCH_ID reconcile to CN_POSTING_BATCHES_ALL.

Queries should always constrain by ORG_ID to respect multi-org security and to exploit the unique index.

Related Objects

The table participates in a dense web of foreign-key relationships, the most significant of which are: