Search Results cn_calc_sub_validations_all




Overview
Explore in AI Chat

Overview

The CN_CALC_SUB_VALIDATIONS_ALL table is a transactional table within the Oracle Incentive Compensation (CN) module of Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. It records the validation results produced when a compensation plan submission batch is processed by the Incentive Compensation calculation engine. Each row represents a validation outcome for a specific combination of a calculation submission batch and a compensation plan, capturing whether the plan passed or failed the validation checks applied during submission processing.

The table operates as a child of both the submission batch framework and the compensation plan definition. Because it stores descriptive validation attributes tied to an existing batch-plan combination rather than defining relationships between independent business entities, the ETRM heuristic data vault classification places it as standalone. In data vault modeling terms this suggests the object behaves more like a satellite — a descriptive record attached to a business key — rather than a hub or link. This classification is a modeling suggestion only; the table is physically implemented as a conventional Oracle EBS transactional table in the CN schema.

Key Information Stored

The documented physical schema contains ten columns. The most significant are described below.

  • ORG_ID — The operating unit identifier, enforcing multi-org partitioning and enabling operating-unit-level security in queries and reports.
  • CALC_SUB_BATCH_ID — Identifies the parent calculation submission batch. This is a foreign key to CN_CALC_SUBMISSION_BATCHES_ALL and forms the leading portion of the business key.
  • COMP_PLAN_ID — Identifies the compensation plan against which validation was performed. This is a foreign key to CN_COMP_PLANS_ALL and completes the unique business key with CALC_SUB_BATCH_ID.
  • AFFECTED_REPS — The count or indicator of representatives affected by the validation result for that plan, which supports impact assessment during batch review.
  • SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, used for row-level access control consistent with EBS security group architecture.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard WHO column tracking, with LAST_UPDATE_LOGIN capturing the session that last modified the row.

No surrogate primary key is documented in the ETRM metadata. The business-key candidate is defined by the unique index CN_CALC_SUB_VALIDATION_U1 on the composite of CALC_SUB_BATCH_ID and COMP_PLAN_ID, confirming that a batch may have at most one validation record per compensation plan.

Common Use Cases and Queries

A primary use case is diagnosing why a calculation submission batch was rejected or partially processed. Report developers join this table to the submission batch and plan tables to list failed validation results for a given batch.

SELECT v.calc_sub_batch_id, p.plan_name, v.affected_reps,
       v.creation_date, v.last_updated_by
FROM   cn_calc_sub_validations_all v,
       cn_comp_plans_all p
WHERE  v.comp_plan_id = p.comp_plan_id
AND    v.calc_sub_batch_id = :batch_id
AND    v.org_id = :org_id;

Additional scenarios include impact analysis to determine how many representatives are affected by validation failures before rerunning a batch, and audit reporting that traces who created or changed validation rows via the WHO columns. Because the table is partitioned by ORG_ID, all production queries should include an operating unit predicate to satisfy row-level security and improve performance.

Related Objects

The following objects are most significant to validation reporting and integration.

  • CN_CALC_SUBMISSION_BATCHES_ALL — parent batch table joined on CALC_SUB_BATCH_ID; supplies batch status and submission metadata.
  • CN_COMP_PLANS_ALL — plan definition table joined on COMP_PLAN_ID; supplies plan name and status.
  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID to resolve security group ownership and row-level access.
  • Incentive Compensation calculation and submission engine APIs — the concurrent programs that populate this table during batch validation.
  • CN_CALC_SUBMISSION_BATCH_DETAILS — typically queried alongside for representative-level batch detail.

Collectively these objects place CN_CALC_SUB_VALIDATIONS_ALL at the intersection of submission batch processing and compensation plan configuration, making it a focused audit and diagnostics table within the Incentive Compensation module.