Search Results territory_type_name

Overview

APPS.JTF_TERR_SRCH_ADV_GEN_V is a database view in the Oracle E-Business Suite Territory Management (ETRM) schema, owned by the APPS account and available in both 12.1.1 and 12.2.2. It presents a denormalized, human-readable projection of territory definitions held in the JTF_TERR table, enriching each territory record with descriptive names resolved from related lookups and self-referencing parent records. The view is used by the Advanced Territory Search functionality, particularly in the Resource Manager and territory administration flows, where users search and inspect territories by attributes such as name, type, rank, parent, template, or escalation relationships.

Because it resolves foreign keys into display text (for example, resolving TERRITORY_TYPE_ID to TERRITORY_TYPE_NAME), the view is a convenient reporting and integration surface. Instead of requiring callers to re-implement multiple joins, the view returns fully labelled territory rows suitable for concurrent programs, BI Publisher reports, OA Framework search regions, and PL/SQL APIs. The user search term "territory_type_name" maps directly to the TERRITORY_TYPE_NAME column defined in this view, sourced from the JTF_TERR_TYPES table.

Underlying Base Objects

The view is defined over the following documented base objects, referenced through APPS synonyms: JTF_SOURCES_ALL, JTF_TERR, JTF_TERR_TYPES, and JTF_TERR_USGS. The JTF_TERR synonym is consumed multiple times under four distinct aliases (JT7, JT8, JT9, JT10), enabling the view to resolve the parent territory, escalation territory, and template territory in a single pass without recursive queries.

Joins are constructed as follows. JT7 is the driving territory row. JT8 supplies the parent name via JT7.PARENT_TERRITORY_ID = JT8.TERR_ID (inner join, since a parent is expected). JT9 supplies the escalation territory name through JT7.ESCALATION_TERRITORY_ID = JT9.TERR_ID (+), and JT10 supplies the template territory name through JT7.TEMPLATE_TERRITORY_ID = JT10.TERR_ID (+); both are outer joins, so territories without escalation or template relationships are still returned. JTF_TERR_TYPES (JTY) is outer-joined on JT7.TERRITORY_TYPE_ID = JTY.TERR_TYPE_ID (+), populating TERRITORY_TYPE_NAME. JTF_TERR_USGS (JTU) is outer-joined on JT7.TERR_ID = JTU.TERR_ID (+), and JTF_SOURCES_ALL (JS) is joined on JTU.SOURCE_ID = JS.SOURCE_ID to supply the MEANING column describing the territory usage source.

Key Columns

• TERR_ID — Primary identifier of the territory; join key to other ETRM objects.
• TERRITORY_NAME — Display name of the territory, aliased from JTF_TERR.NAME (JT7).
• DESCRIPTION, RANK, START_DATE_ACTIVE, END_DATE_ACTIVE — Descriptive and effective-dating attributes of the territory.
• PARENT_TERRITORY_NAME — Name of the parent territory resolved from the self-join alias JT8.
• ESCALATION_TERRITORY_FLAG and ESCALATION_TERR_NAME — Indicator and resolved name of the escalation territory (JT9, outer join).
• TEMPLATE_FLAG and TEMPLATE_TERR_NAME — Indicator and resolved name of the template territory (JT10, outer join).
• TERRITORY_TYPE_NAME — The territory type name from JTF_TERR_TYPES. This is the column matched by the user's search term and is central to filtering territories by classification.
• MEANING — Lookup meaning for the territory usage source from JTF_SOURCES_ALL.

Common Use Cases and Queries

Typical usage includes LOVs and query regions in territory administration, ad-hoc SQL for data validation, and reports listing territory hierarchies. The following sample returns all active territories with their type and parent:

• SELECT terr_id, territory_name, territory_type_name, parent_territory_name, rank FROM apps.jtf_terr_srch_adv_gen_v WHERE territory_type_name = 'Sales' ORDER BY rank;
• SELECT territory_name, escalation_terr_name, template_terr_name FROM apps.jtf_terr_srch_adv_gen_v WHERE template_flag = 'Y';
• SELECT territory_name, meaning FROM apps.jtf_terr_srch_adv_gen_v WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE + 1);

Because the view outer-joins several lookup tables, rows may return null for type, escalation, template, or source meaning; queries that require these values should apply appropriate filters. All references via APPS synonyms respect the standard EBS security and synonym conventions.