Results for “location_area_code”

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

Overview

JTF.JTF_LOC_AREAS_B is a CRM Foundation (JTF) base table that serves as a generic repository for geographical location definitions which are not stored elsewhere in the Oracle E-Business Suite data model. It provides the canonical definition of a location area — for example a country, state, city, region, or postal grouping — and is distinct from the address objects in the Trading Community Architecture (HZ) tables, which hold party and site addresses rather than reusable geographic taxonomy.

Because the table is owned by the JTF schema and carries the _B suffix, it is the base (language-independent) entity. Translatable attributes are held separately in JTF_LOC_AREAS_TL, and the two are joined by LOCATION_AREA_ID. In Oracle EBS 12.1.1 and 12.2.2 the table remains a VALID, supported object and participates in territory assignment, hierarchical geographies, and postal-code mapping.

The relationship metadata classifies this object as hub-leaning. In Data Vault terms it is best modelled as a hub: its primary key LOCATION_AREA_ID is a stable surrogate identifier that other tables reference, while descriptive and mutable attributes (activation dates, object version, audit columns) would typically be stripped into a satellite. This is a modelling suggestion arising from the foreign-key topology rather than a documented Oracle classification.

Key Information Stored

Of the twenty documented columns, the following are the most significant:

• LOCATION_AREA_ID — the surrogate primary key, enforced by JTF_LOC_AREAS_B_PK. Every dependent table joins on this value.
• LOCATION_AREA_CODE — the human-readable business identifier for the area.
• LOCATION_TYPE_CODE — the classification of the area (country, state, city, region, and so on), validated against JTF_LOC_TYPES_B.
• PARENT_LOCATION_AREA_ID — a self-referencing column that builds the geographic hierarchy within this single table; a city row points to its state, a state to its country.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective-dating window controlling whether the area is currently usable.
• ORIG_SYSTEM_ID and ORIG_SYSTEM_REF — identify the source system that created the record, supporting data migration and multi-origin loads.
• SECURITY_GROUP_ID — the operating-unit security grouping, foreign keyed to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — the optimistic-locking token used by the Oracle Application Framework (OAF) during concurrent updates.
• ZD_EDITION_NAME — the editioning column introduced by the 12.2 Online Patching model.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, plus REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID for concurrent-manager traceability.

The unique index JTF_LOC_AREAS_B_U1 spans LOCATION_AREA_ID and ZD_EDITION_NAME, confirming that the business key of an area is scoped by edition in 12.2.

Common Use Cases and Queries

The most frequent requirement is resolving a location area code to its identifier, and then walking the hierarchy. A typical lookup is:

SELECT location_area_id, location_area_code, location_type_code
FROM   jtf_loc_areas_b
WHERE  location_area_code = :code
AND    SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
                   AND NVL(end_date_active, SYSDATE);

Because translation resides in the TL table, reporting that requires the display name joins the two:

SELECT b.location_area_id, t.location_area_name, b.location_type_code
FROM   jtf_loc_areas_b b, jtf_loc_areas_tl t
WHERE  b.location_area_id = t.location_area_id
AND    t.language = USERENV('LANG');

Hierarchical reporting uses a CONNECT BY walk over the self-referencing parent column:

SELECT LEVEL, location_area_id, location_area_code
FROM   jtf_loc_areas_b
START WITH parent_location_area_id IS NULL
CONNECT BY PRIOR location_area_id = parent_location_area_id;

Territory and quota reports join OKR_IP_RGTS_TXN_TERR to resolve a territory identifier to its geographic area, while postal-code-driven addressing joins JTF_LOC_POSTAL_CODES on LOCATION_AREA_ID. Validation routines and OAF pages reference JTF_LOC_TYPES_B to restrict the allowable type codes.

Related Objects

• JTF_LOC_TYPES_B — joined via LOCATION_TYPE_CODE; defines the permitted area classifications.
• JTF_LOC_AREAS_TL — the translation table, joined on LOCATION_AREA_ID; supplies language-specific names.
• JTF_LOC_AREAS_B (self) — joined through PARENT_LOCATION_AREA_ID to LOCATION_AREA_ID for hierarchical traversal.
• JTF_LOC_HIERARCHIES_B — references this table through nine columns: POSTAL_CODE_ID, AREA1_ID, AREA2_ID, COUNTRY_ID, STATE_ID, COUNTRY_REGION_ID, STATE_REGION_ID, and CITY_ID; this is the principal consumer of the area definitions.
• JTF_LOC_POSTAL_CODES — joins on LOCATION_AREA_ID, associating postal codes with the areas they belong to.
• OKR_IP_RGTS_TXN_TERR — references areas via TERRITORY_ID in territory-assignment transactions.
• HZ_ORIG_SYSTEMS_B — joined through ORIG_SYSTEM_ID to trace the originating system.
• FND_SECURITY_GROUPS — joined through SECURITY_GROUP_ID to enforce multi-organization security.