Results for “jty_dea_values_idx_details”

50+ results




Overview
Explore in AI Chat

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

Overview

JTY_DEA_VALUES_IDX_DETAILS is a table owned by the JTF schema (CRM Foundation product family) within Oracle E-Business Suite 12.1.1 and 12.2.2. As documented in the ETRM repository, its purpose is to store indexes for date effectivity assignment. In practical terms, this table persists the low-level detail rows that describe how date-effective value sets are indexed, supporting the broader date effectivity framework (often referenced as DEA — Date Effectivity Assignment) that governs records with effective start and end dates across CRM and related modules.

The table is classified within the ETRM metadata as a standalone object under a heuristic Data Vault classification, meaning it does not participate in a strongly modeled hub/link/satellite chain derived from foreign key structure. From a modeling perspective, it behaves most like a detail or satellite-style structure attached to a header row (via DEA_VALUES_IDX_HEADER_ID) rather than a true business hub, because its columns describe attributes and index mappings rather than independent business entities. The presence of standard EBS audit columns (WHO columns) confirms it is a transactional/configuration detail table with full row-level change tracking.

Key Information Stored

The table contains 12 documented columns. The most significant are summarized below.

  • DEA_VALUES_IDX_DETAILS_ID — the surrogate primary key uniquely identifying each detail row.
  • DEA_VALUES_IDX_HEADER_ID — the foreign reference to the corresponding header record; it groups detail rows under a single index definition and is the principal join key to the parent table.
  • VALUES_COL_MAP — stores the mapping of value columns associated with the index, defining which value-set columns participate in the index.
  • INPUT_SELECTIVITY — records the selectivity factor for the input, used by the index/effectivity engine to optimize evaluation.
  • INPUT_ORDINAL_SELECTIVITY — records the ordinal-based selectivity for the input, providing positional selectivity information alongside the general selectivity.
  • SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, enforcing the multi-org/multi-tenant security context under which the index detail is visible.
  • OBJECT_VERSION_NUMBER — supports optimistic locking and concurrency control, standard in EBS 12.2.x tables.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard EBS audit (WHO) columns capturing who created and last modified each row and when.

No unique business-key index is documented in the provided metadata beyond the surrogate key, so DEA_VALUES_IDX_DETAILS_ID is the definitive row identifier, with the combination of DEA_VALUES_IDX_HEADER_ID plus ordinal/selectivity attributes serving as the practical business locator within a header.

Common Use Cases and Queries

This table is typically queried during diagnosis of date effectivity behavior, index optimization, and CRM value-set configuration. A common pattern retrieves all detail rows for a given header:

  • SELECT d.dea_values_idx_details_id, d.values_col_map, d.input_selectivity, d.input_ordinal_selectivity FROM jtf.jty_dea_values_idx_details d WHERE d.dea_values_idx_header_id = :header_id ORDER BY d.input_ordinal_selectivity;
  • Reporting on selectivity distribution across headers helps identify poorly configured indexes that degrade date effectivity resolution performance.
  • Security-scoped extracts join to FND_SECURITY_GROUPS via SECURITY_GROUP_ID to honor access control.
  • Change-audit reporting filters on LAST_UPDATE_DATE / LAST_UPDATED_BY to track configuration drift over time.

Related Objects

  • JTY_DEA_VALUES_IDX_HEADER — parent table joined on DEA_VALUES_IDX_HEADER_ID = the header's primary key; the principal dependency for this object.
  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID to enforce security grouping.
  • JTF date effectivity value / assignment views and packages — consume these detail rows when resolving effective-dated values.
  • FND audit objects — LAST_UPDATED_BY and CREATED_BY relate to FND_USER.