Search Results jtf_qual_type_denorm_v




Overview
Explore in AI Chat

Overview

The JTF_QUAL_TYPE_DENORM_V view is a de-normalized reporting object owned by the APPS schema within the JTF - CRM Foundation product module of Oracle E-Business Suite. Its documented purpose is to present a flattened representation of the JTF_QUAL_TYPES table, whose physical storage is exposed through the synonym JTF_QUAL_TYPES_ALL. In EBS 12.1.1 and 12.2.2, the object holds a status of VALID and is commonly referenced in CRM qualification and lead/opportunity reporting, where consumers require a simple two-column projection of qualification type identifiers paired with their associated related entities.

Rather than exposing the five discrete RELATED_ID1 through RELATED_ID5 columns of the base table, this view unpivots them into a single RELATED_ID column through a series of UNION operations. This structure simplifies joins and reporting against qualification type relationships, making it useful in integration and business intelligence contexts where a normalized, row-per-relationship shape is preferable to a wide, sparsely populated table.

Underlying Base Objects

The view is defined exclusively over JTF_QUAL_TYPES_ALL, documented as a SYNONYM in the ETRM metadata and resolving to the JTF_QUAL_TYPES table in the APPS schema. The view text comprises five SELECT statements joined by UNION, each projecting QUAL_TYPE_ID and one of the five RELATED_ID columns. Each branch applies an IS NOT NULL predicate to its respective RELATED_ID column, ensuring that only populated relationship slots contribute rows to the result set.

Because UNION (rather than UNION ALL) is used, duplicate QUAL_TYPE_ID and RELATED_ID combinations produced across the five branches are collapsed into a single row. The view therefore inherits the row-level security and multi-org characteristics of the underlying _ALL table, and it should be treated as a dependent object when analyzing the impact of changes to JTF_QUAL_TYPES_ALL.

Key Columns

  • QUAL_TYPE_ID — The primary identifier of the qualification type. This column corresponds directly to QUAL_TYPE_ID in the base table and is the primary join key to qualification type definitions and related CRM entities.
  • RELATED_ID — A consolidated column derived from any of RELATED_ID1 through RELATED_ID5 in the base table. Each returned row represents one non-null related entity reference associated with the qualification type.

Because the view flattens five source columns into one, the specific origin column (RELATED_ID1 through RELATED_ID5) is not exposed in the projection. Consumers requiring knowledge of which slot the relationship originated from must query the base table directly.

Common Use Cases and Queries

Typical use cases include enumerating all related entities tied to a qualification type without writing multi-branch UNION logic, and joining qualification types to downstream CRM objects for lead, opportunity, or resource qualification reporting.

  • Listing all relationships for a specific qualification type:
SELECT qual_type_id, related_id
FROM   apps.jtf_qual_type_denorm_v
WHERE  qual_type_id = :p_qual_type_id;
  • Counting distinct related entities per qualification type:
SELECT qual_type_id, COUNT(DISTINCT related_id) rel_count
FROM   apps.jtf_qual_type_denorm_v
GROUP  BY qual_type_id
ORDER  BY rel_count DESC;
  • Searching for a specific related entity across all qualification types:
SELECT qual_type_id
FROM   apps.jtf_qual_type_denorm_v
WHERE  related_id = :p_related_id;

The last query is particularly relevant to searches on related_id5 and the other numbered slots, since the view resolves the originating column into the single RELATED_ID column, allowing a single equality predicate to cover all five underlying relationship slots.