Search Results jtf_terr_qual_types

Overview

APPS.JTF_QUAL_TYPES is a reporting and integration view in the Oracle E-Business Suite CRM Foundation (JTF) schema. It exposes the qualification type definitions used by Oracle Territory Manager (also referred to as ETRM), the subsystem that governs territory assignment, qualifiers, and the rules that determine which sales or service resources are credited with a given transaction, customer, or opportunity. The view presents one row per qualification type, describing both the metadata of the type and the SQL fragments that the territory engine uses to evaluate it.

The view name is frequently encountered by administrators searching on "jtf_terr_qual_types", which is the lookup type used internally to resolve the descriptive meaning of each qualification type. The exposed DESCRIPTION column is not stored in the qualification table itself; it is derived at query time by joining the NAME column against the JTF_TERR_QUAL_TYPES lookup type in FND_LOOKUPS. Because the join is an inner join, only qualification types whose names correspond to seeded or defined lookup codes appear in the result set.

Consistent with other EBS reporting views, the key columns are edition-aware: the view filters and projects the ORG_ID column so that multi-org installations return a single, edition-scoped definition per qualification type instead of the multiple rows present in the underlying _ALL table.

Underlying Base Objects

The ETRM metadata documents three referenced objects: the FND_LOOKUPS view, the JTF_QUAL_TYPES_ALL synonym, and the FND_GLOBAL package.

• JTF_QUAL_TYPES_ALL — the driving table, aliased QTY in the view text. It stores the qualification type records, including the dynamic SQL fragments and related identifier columns.
• FND_LOOKUPS — the Oracle Application Object Library lookup view, aliased LKP. It supplies the human-readable MEANING, exposed as DESCRIPTION, for each qualification type name.
• FND_GLOBAL — the standard EBS session context package. Its presence in the referenced object list indicates the view participates in multi-org and security-group filtering, typically supplying the operating unit or org context used to scope ORG_ID.

The join predicate is QTY.NAME = LKP.LOOKUP_CODE AND LKP.LOOKUP_TYPE = 'JTF_TERR_QUAL_TYPES', making the lookup type the semantic anchor of the view.

Key Columns

• QUAL_TYPE_ID — primary identifier of the qualification type; used as the foreign key from territory qualifier and rule definitions.
• NAME — the internal code of the qualification type; must match a lookup code of type JTF_TERR_QUAL_TYPES.
• DESCRIPTION — the lookup MEANING; the display label shown in territory administration and reporting.
• SELECT_CLAUSE / WHERE_CLAUSE — the SQL fragments that the territory engine dynamically composes to qualify transactions against the type.
• VIEW_NAME / VIEW_DDL_FILENAME — the database view (and its DDL script) that materializes the qualification for evaluation.
• RELATED_ID1 through RELATED_ID5 — denormalized context identifiers used by territory administration windows and dependent logic.
• ORG_ID — operating unit / organization identifier supporting multi-org data partitioning.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS who-columns for audit and change tracking.

Common Use Cases and Queries

Typical scenarios include confirming which qualification types are available before defining territory qualifiers, auditing the SQL fragments that drive qualification logic during a territory troubleshooting exercise, and joining qualification types to usage counts to understand which types are actively referenced.

List all available qualification types with their descriptive labels:

SELECT qual_type_id, name, description
FROM   apps.jtf_qual_types
ORDER  BY name;

Reconcile the view against its source to detect orphaned qualification type names that lack a matching lookup code:

SELECT qty.qual_type_id, qty.name
FROM   apps.jtf_qual_types_all qty
WHERE  NOT EXISTS
       (SELECT 1 FROM apps.fnd_lookups lkp
        WHERE  lkp.lookup_type = 'JTF_TERR_QUAL_TYPES'
        AND    lkp.lookup_code = qty.name);

Inspect the SQL fragments used by a specific type:

SELECT name, select_clause, where_clause, view_name
FROM   apps.jtf_qual_types
WHERE  name = :qualification_name;

Because the view resolves the lookup MEANING at runtime, changes to lookup meanings are reflected immediately without updates to the qualification table. Consumers should nevertheless treat SELECT_CLAUSE, WHERE_CLAUSE, and VIEW_NAME as internal ETRM implementation details and avoid direct modification.