Search Results org_fk




Overview
Explore in AI Chat

Overview

BIL_DIMV_PLAN_ELEMENTS is a read-only dimensional view belonging to the BIL (Sales Intelligence) product family, a module now classified as obsolete within Oracle EBS 12.1.1 and 12.2.2. As its description states, the view exposes "compensation plan elements" — the quotas, targets, incentive types, and payment amounts that make up a sales compensation plan assigned to an organization. Its role in the reporting and integration stack is to present a flattened, denormalized dimension that downstream Sales Intelligence analytics, OBIEE repositories, or custom extracts can consume without repeatedly joining the underlying quota and lookup tables.

Because BIL is obsolete, the view is retained primarily for backward compatibility. The ETRM metadata explicitly notes that it is "not implemented in this database" and carries no documented base-object registration, so implementations must treat it as a legacy artifact. A WITH READ ONLY clause in the view definition guarantees that no DML can be issued against it.

Underlying Base Objects

Although the metadata lists no documented referenced base objects, the embedded view text reveals the physical lineage. The primary branch joins four tables:

A second branch is UNION ALL'd onto the first and draws from FND_LOOKUPS, filtered to LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999'. This branch supplies a synthetic placeholder row, coercing lookup columns into numeric quota-style values. This design effectively instantiates the common dimensional pattern of a real record set plus a default "unknown" member, so reports can join without losing rows when no quota is assigned. The ORG_FK value in this branch is derived from the lookup code rather than from a true organization identifier, which is an important caveat for anyone mapping the column to HR_OPERATING_UNITS or similar org dimensions.

Key Columns

  • QUOTA_PK — surrogate key for the plan element; in the synthetic branch it derives from LOOKUP_CODE.
  • ORG_FK — the organization foreign key that users commonly search for. In the real branch it originates from QUOTA.ORG_ID, aligning to the multi-org operating unit model; in the placeholder branch it is coerced from the lookup code.
  • QUOTA_NAME / QUOTA_DESCRIPTION — descriptive attributes; sourced from QUOTA.NAME/DESCRIPTION or FL.MEANING/FL.DESCRIPTION.
  • QUOTA_TARGET / QUOTA_PAYMENT — numeric compensation measures (target attainment versus payment amount).
  • QUOTA_PERIOD_TYPE — the interval type name (e.g., monthly, quarterly).
  • QUOTA_INCENTIVE_TYPE — the incentive classification code or its translated meaning.
  • QUOTA_PERFORMANCE_GOAL — the goal threshold tied to the plan element.
  • START_DATE / END_DATE — active date range of the element.
  • ID / VALUE — presentation aliases used by the BI layer for dimension key and display value.

Common Use Cases and Queries

Typical usage involves driving compensation dashboards, validating quota assignments per operating unit, or extracting plan elements into a data warehouse. A minimal query joining back to the operating unit, mindful of the synthetic ORG_FK caveat, might read:

SELECT p.QUOTA_PK, p.ORG_FK, p.QUOTA_NAME, p.QUOTA_TARGET, p.QUOTA_PAYMENT, p.START_DATE, p.END_DATE FROM BIL_DIMV_PLAN_ELEMENTS p WHERE p.ORG_FK = :org_id AND SYSDATE BETWEEN p.START_DATE AND p.END_DATE;

To isolate genuine records from the placeholder row, filter with WHERE QUOTA_PK > 0 or add a predicate on QUOTA_NAME. Because the view is marked READ ONLY and its source module is obsolete, new development should verify column availability against the actual database before relying on it, and consider migrating logic to the underlying CN_QUOTAS_ALL tables directly.