Results for “area1_name”

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

Overview

JTF_LOC_HIERARCHIES_VL is a validation-level (VL) view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the JTF – CRM Foundation product family, which provides the shared location, party, and territory infrastructure used across CRM and order-to-cash modules. The view is defined primarily over the JTF_LOC_HIERARCHIES_B base table and joins to JTF_LOC_TYPES_VL and multiple instances of JTF_LOC_AREAS_TL to resolve internal ID and code pairs into user-facing descriptive names.

The "_VL" suffix indicates that the view exposes translated reference data. Each JTF_LOC_AREAS_TL join is filtered by LANGUAGE(+) = USERENV('LANG'), so location area names are returned in the session's current language rather than a fixed language. This makes the view suitable for multilingual reporting and for LOV (list of values) queries where a localized description is required. Because it flattens the hierarchical geography of a location record into a single row, JTF_LOC_HIERARCHIES_VL is a convenient integration and reporting source for CRM location hierarchies.

Underlying Base Objects

The documented base objects referenced by the view are:

• JTF_LOC_HIERARCHIES_B — the driving table, holding the location hierarchy rows including area, country, state, city, and postal codes.
• JTF_LOC_TYPES_VL — provides the location type name (LOCATION_TYPE_NAME) for the LOCATION_TYPE_CODE on the hierarchy.
• JTF_LOC_AREAS_TL — the translated location area table, aliased seven times (LOC1 through LOC7) to resolve AREA1_ID, AREA2_ID, COUNTRY_ID, COUNTRY_REGION_ID, STATE_ID, STATE_REGION_ID, and CITY_ID into LOCATION_AREA_NAME values.
• JTF_LOC_POSTAL_CODES — supplies the POSTAL_CODE_START and POSTAL_CODE_END range for the hierarchy's postal code.

The joins to JTF_LOC_AREAS_TL are outer joins (indicated by the (+) syntax), so a hierarchy row is returned even when one or more of its area IDs is null. The join to JTF_LOC_TYPES_VL is an inner join.

Key Columns

• LOCATION_HIERARCHY_ID — primary key of the hierarchy row.
• LOCATION_TYPE_CODE / LOCATION_TYPE_NAME — the type of location and its translated description.
• AREA1_ID, AREA1_CODE, AREA1_NAME (and the parallel AREA2 set) — first and second hierarchical area attributes, with AREA1_NAME and AREA2_NAME resolved via LOC1 and LOC2.
• COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME, COUNTRY_REGION_ID, COUNTRY_REGION_CODE, COUNTRY_REGION_NAME, STATE_ID, STATE_CODE, STATE_NAME, STATE_REGION_ID, STATE_REGION_CODE, STATE_REGION_NAME, CITY_ID, CITY_CODE, CITY_NAME — each geography level with its ID, code, and translated name. The user query term location_area_name corresponds directly to the LOCATION_AREA_NAME column sourced from JTF_LOC_AREAS_TL and surfaced as the *_NAME columns.
• POSTAL_CODE_ID, POSTAL_CODE_START, POSTAL_CODE_END — the postal code range associated with the hierarchy.
• START_DATE_ACTIVE, END_DATE_ACTIVE — the effective dating window for the hierarchy.
• ROW_ID, OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, CREATED_BY and related audit columns — standard WHO columns and the row identifier for locked updates.

Common Use Cases and Queries

Typical uses include localized geography lookups for CRM screens, hierarchical address reporting, and downstream integrations that require a single flattened location record with readable names.

To retrieve the localized names for a given hierarchy:

• SELECT location_hierarchy_id, location_type_name, country_name, state_name, city_name, area1_name, area2_name FROM jtf_loc_hierarchies_vl WHERE location_hierarchy_id = :p_id;

To find the LOCATION_AREA_NAME values associated with a specific city or area:

• SELECT location_hierarchy_id, city_code, city_name, area1_name FROM jtf_loc_hierarchies_vl WHERE city_name LIKE 'San%';

To build a report of hierarchies by country, excluding inactive rows:

• SELECT country_name, state_name, city_name, location_type_name FROM jtf_loc_hierarchies_vl WHERE country_code = 'US' AND (end_date_active IS NULL OR end_date_active > SYSDATE) ORDER BY state_name, city_name;

Because the view is read-only and based on the _B table, DML should be performed against the underlying base tables or their corresponding APIs rather than through the view. Report logic should also account for the outer joins, which may return null area names where hierarchy attributes are not populated.