Results for “geo_terr_value_id”

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

Overview

JTF_TTY_GEO_TERR_VALUES is a CRM Foundation (JTF) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores geography territory values, most notably postal code assignments. It functions as the intersection between two master entities maintained elsewhere in the territory management model: a geography definition (held in JTF_TTY_GEOGRAPHIES) and a geography territory (held in JTF_TTY_GEO_TERR). By resolving the many-to-many relationship between geographies and territories, the table determines which geography values — such as postal codes, states, or cities — are included in a given territory used by Territory Manager and related CRM routing, assignment, and scoring engines.

The ETRM documentation classifies this object heuristically as a link table within a Data Vault style model. That classification reflects its structure: it carries two foreign keys, one to JTF_TTY_GEO_TERR and one to JTF_TTY_GEOGRAPHIES, and its natural role is to associate those two hubs. In practice, the table behaves as a junction or association entity rather than an independent business hub or a descriptive satellite.

Key Information Stored

The table has nine documented columns. The surrogate primary key is GEO_TERR_VALUE_ID, which is also enforced through the unique index JTF_TTY_GEO_TERR_VALUES_U1 and the constraint JTF_TTY_GEO_TERR_VALUES_PK. Because this is the only documented unique index, GEO_TERR_VALUE_ID is the sole candidate business key identified in the metadata; the pairing of GEO_TERRITORY_ID and GEO_ID is not documented as uniquely constrained.

• GEO_TERR_VALUE_ID — surrogate primary key uniquely identifying each row.
• GEO_TERRITORY_ID — foreign key to JTF_TTY_GEO_TERR, identifying the territory to which the geography value belongs.
• GEO_ID — foreign key to JTF_TTY_GEOGRAPHIES, identifying the specific geography value (for example a postal code) assigned to that territory.
• OBJECT_VERSION_NUMBER — optimistic locking column supporting concurrent updates through the OAF/BC4J framework.
• CREATED_BY, CREATION_DATE — standard who-column audit attributes recording row creation.
• LAST_UPDATED_BY, LAST_UPDATE_DATE — audit attributes recording the most recent modification.
• LAST_UPDATE_LOGIN — login identifier associated with the last update.

Common Use Cases and Queries

The principal use case is resolving which geographies belong to a territory, or inversely, which territory owns a given postal code. A typical lookup joins the value table to the geography master to expose the postal code and its owning territory:

• Territory membership listing: SELECT g.geo_id, g.postal_code, v.geo_territory_id FROM jtf.jtf_tty_geo_terr_values v, jtf.jtf_tty_geographies g WHERE v.geo_id = g.geo_id;
• Postal code to territory resolution: filter the same join by the desired postal code to return its territory.
• Territory coverage reporting: group rows by GEO_TERRITORY_ID to count assigned geographic values per territory.
• Data quality auditing: identify orphaned rows in JTF_TTY_GEO_WEBADI_INTERFACE whose GEO_TERR_VALUE_ID no longer matches a value row, indicating stale web-ADI assignment data.
• Concurrent-update diagnostics: inspect OBJECT_VERSION_NUMBER, LAST_UPDATED_BY, and LAST_UPDATE_DATE to trace recent territory-geography maintenance.

Related Objects

The following objects are the most significant dependencies, based on the documented foreign key relationships:

• JTF_TTY_GEO_TERR — parent territory definition; joined via JTF_TTY_GEO_TERR_VALUES.GEO_TERRITORY_ID.
• JTF_TTY_GEOGRAPHIES — geography master supplying postal codes and other geography values; joined via JTF_TTY_GEO_TERR_VALUES.GEO_ID.
• JTF_TTY_GEO_WEBADI_INTERFACE — interface table that references this object through GEO_TERR_VALUE_ID, used to load geography territory values from web/ADI sources.
• JTF_TTY_GEO_TERR_VALUES_PK — the enforced primary key constraint on GEO_TERR_VALUE_ID.
• JTF_TTY_GEO_TERR_VALUES_U1 — the unique index on GEO_TERR_VALUE_ID that backs the business-key candidate.

Collectively, these relationships position JTF_TTY_GEO_TERR_VALUES as the operational bridge connecting geography master data to territory definitions in Oracle CRM Foundation.