Results for “itd_payment”

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

Overview

CN_PERIOD_QUOTAS_ALL is a transactional table in the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores period-level target and payment amounts associated with a plan element, effectively recording how a quota assigned to a participant is distributed and achieved across reporting periods. Within the compensation calculation lifecycle, this table links the quota definition (the "what was expected" side) to the period-based measurement of attainment and payment (the "what was achieved" side). It supports year-to-date and inception-to-date accumulation, quarter and year attribution, and descriptive flexfield capture for customer-specific extensions.

From a Data Vault modeling perspective, the mined foreign-key structure classifies CN_PERIOD_QUOTAS_ALL heuristically as a link object. It sits at the intersection of two dimensions — the period and the quota — and carries the measurable outcomes (target and payment amounts) that arise from that relationship. Modelers may therefore treat it as a link table, optionally paired with a satellite for the mutable numeric and attribute columns.

Key Information Stored

The table contains 35 documented columns. The most significant are:

• PERIOD_QUOTA_ID — the surrogate primary key defined by CN_PERIOD_QUOTAS_PK, uniquely identifying each period-quota record.
• PERIOD_ID — foreign key to CN_PERIOD_STATUSES_ALL, identifying the compensation period.
• QUOTA_ID — foreign key to CN_QUOTAS_ALL, identifying the parent quota assignment.
• PERIOD_TARGET / ITD_TARGET — the target amount for the period and the inception-to-date target respectively.
• PERIOD_PAYMENT / ITD_PAYMENT — the commission or incentive payment earned in the period and inception-to-date.
• PERFORMANCE_GOAL / PERFORMANCE_GOAL_ITD — periodic and inception-to-date performance goal values used in attainment calculations.
• QUARTER_NUM, PERIOD_YEAR — denormalized period attributes enabling efficient time-based reporting.
• ORG_ID — the multi-org operating unit identifier; combined with PERIOD_QUOTA_ID and with PERIOD_ID + QUOTA_ID it forms the documented unique business keys.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS supporting row-level security.
• ATTRIBUTE_CATEGORY through ATTRIBUTE15 — descriptive flexfield columns for extensibility.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF/ADF framework, plus the standard WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

The surrogate key is PERIOD_QUOTA_ID; the business-key candidates are captured by two unique indexes: CN_PERIOD_QUOTAS_U1 (PERIOD_QUOTA_ID, ORG_ID) and the more meaningful CN_PERIOD_QUOTAS_U2 (PERIOD_ID, QUOTA_ID, ORG_ID), which enforces one record per period per quota.

Common Use Cases and Queries

Typical scenarios include quota-versus-attainment reporting, period payment reconciliation, and year-to-date dashboard feeds. A common pattern retrieves all periods for a given quota:

• SELECT period_id, period_target, period_payment, itd_target, itd_payment, quarter_num, period_year FROM cn_period_quotas_all WHERE quota_id = :p_quota_id AND org_id = :p_org_id ORDER BY period_year, quarter_num;

Joining to the period definition to surface period names and statuses is frequent:

• SELECT q.quota_id, p.period_name, q.period_target, q.period_payment FROM cn_period_quotas_all q, cn_period_statuses_all p WHERE q.period_id = p.period_id AND q.org_id = :p_org_id;

Analysts frequently aggregate ITD_PAYMENT by PERIOD_YEAR or QUARTER_NUM to produce trending reports, and filter by SECURITY_GROUP_ID to honor compensation security rules. Because the table carries both periodic and cumulative measures, it is often read by concurrent programs and custom BI Publisher reports rather than being updated directly; writes should generally flow through the standard Incentive Compensation quota loading and calculation processes.

Related Objects

• CN_QUOTAS_ALL — the parent quota table, joined via CN_PERIOD_QUOTAS_ALL.QUOTA_ID; provides the header assignment to which period rows belong.
• CN_PERIOD_STATUSES_ALL — the period definition table, joined via CN_PERIOD_QUOTAS_ALL.PERIOD_ID; supplies period names and open/closed status.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID to enforce compensation row-level security.
• CN_PLAN_ELEMENTS / CN_PLANS — upstream plan configuration that ultimately drives the quota and its period allocation.
• CN_PERIOD_QUOTAS_PK / CN_PERIOD_QUOTAS_U1 / CN_PERIOD_QUOTAS_U2 — the primary and unique indexes that define record identity and business keys.
• Incentive Compensation calculation and quota-loading concurrent programs, which populate and consume the target and payment columns.