Search Results cn_pay_element_inputs_all

Overview

CN_PAY_ELEMENT_INPUTS_ALL is a transactional configuration table in the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite. It stores the mapping information that links Pay Element Inputs to Oracle Incentive Compensation (OIC) table columns. In practice, this table defines where a given input value for a quota pay element is sourced when Incentive Compensation processes crediting, quota, and commission calculations. Each row establishes the correspondence between an element input definition and a specific column object belonging to a tab object, effectively acting as the metadata bridge between the pay element input layer and the underlying OIC analytical structures.

From a Data Vault modeling perspective, the ETRM metadata classifies this object as a standalone entity based on its foreign key profile. As a modeling suggestion, it is best treated as a hub-like reference entity keyed on PAY_ELEMENT_INPUT_ID, with a dependent link to FND_SECURITY_GROUPS through SECURITY_GROUP_ID. The table is not a multi-parent link table; its dependencies resolve to a single external reference (security groups), so it behaves more like an independent configuration dimension than a junction table.

Key Information Stored

The table is owned by the CN schema and contains 33 documented columns. The most significant of these are described below. The surrogate primary key is PAY_ELEMENT_INPUT_ID, enforced by the CN_PAY_ELEMENT_INPUTS_PK constraint. A unique index, CN_PAY_ELEMENT_INPUTS_U1, also exists on PAY_ELEMENT_INPUT_ID, confirming it as the single business-key candidate.

• PAY_ELEMENT_INPUT_ID — The primary key and unique identifier for each input-to-column mapping record.
• ORG_ID — The operating unit identifier, enabling multi-org data segregation.
• QUOTA_PAY_ELEMENT_ID — The pay element to which the input belongs, linking the mapping to a specific quota element definition.
• LINE_NUMBER — Controls ordering of the input mapping within a pay element.
• ELEMENT_INPUT_ID — References the element input definition; the input being mapped.
• ELEMENT_TYPE_ID — Identifies the element type classification for the input.
• TAB_OBJECT_ID — Identifies the OIC tab object (the logical table) containing the target column.
• COL_OBJECT_ID — Identifies the specific OIC column object that supplies the input value.
• START_DATE / END_DATE — Effective dating that governs when the mapping is active.
• SECURITY_GROUP_ID — The sole foreign key, referencing FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the framework to detect concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns present on nearly all EBS transactional tables.

The presence of 15 attribute columns (ATTRIBUTE1 through ATTRIBUTE15) plus ATTRIBUTE_CATEGORY follows the standard EBS descriptive flexfield pattern, allowing customer-specific extensions without schema changes.

Common Use Cases and Queries

Functional consultants and developers query this table when diagnosing how quota pay element inputs derive their values during incentive plan setup. Typical scenarios include validating that every required input has a valid column mapping, auditing which OIC table column feeds a given input, and reporting on all mappings associated with a specific pay element or security group.

A representative query resolving inputs to their tab and column objects:

• SELECT pei.pay_element_input_id, pei.quota_pay_element_id, pei.element_input_id, pei.tab_object_id, pei.col_object_id, pei.start_date, pei.end_date FROM cn.cn_pay_element_inputs_all pei WHERE pei.quota_pay_element_id = :p_element_id ORDER BY pei.line_number;
• SELECT pei.* FROM cn.cn_pay_element_inputs_all pei WHERE pei.security_group_id = :p_sec_group AND TRUNC(SYSDATE) BETWEEN pei.start_date AND NVL(pei.end_date, TRUNC(SYSDATE));

Because the table is date-effective, reports should always apply the START_DATE/END_DATE predicates to avoid returning superseded mappings. Multi-org aware queries must additionally filter by ORG_ID. Extraction jobs feeding data warehouses or reconciliation scripts commonly join through QUOTA_PAY_ELEMENT_ID to the parent pay element definition to produce a flattened input-source report.

Related Objects

• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID, the only documented foreign key relationship.
• CN_PAY_ELEMENT_INPUTS_PK / CN_PAY_ELEMENT_INPUTS_U1 — the primary key constraint and unique index enforcing PAY_ELEMENT_INPUT_ID.
• Quota pay element definition tables — joined via QUOTA_PAY_ELEMENT_ID to resolve the owning pay element.
• Element input definition tables — joined via ELEMENT_INPUT_ID and ELEMENT_TYPE_ID to resolve the input semantics.
• OIC tab and column object tables — joined via TAB_OBJECT_ID and COL_OBJECT_ID to resolve the physical source column.
• CN_PAY_ELEMENT_INPUTS_ALL child/related setup tables that share the _ALL multi-org suffix convention and the same security-group model.

Referential integrity is largely enforced at the application layer rather than through declared foreign keys, so any custom reporting against this table should validate cross-references explicitly rather than relying solely on database constraints.