Results for “postal_code_start”

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

Overview

JTF_LOC_POSTAL_CODES is a generic reference table in the JTF (CRM Foundation) schema that stores ranges of postal codes associated with a defined location area. Within Oracle E-Business Suite 12.1.1 and 12.2.2, it is one of the foundational components of the JTF location model, which underpins CRM territory assignment, geography hierarchy handling, and address validation logic across modules such as TeleSales, Service, and Marketing. Rather than holding a single postal code per row, the table captures a contiguous range using start and end values, allowing a location area to be mapped to many postal codes without storing each code individually.

The table carries audit columns standard to EBS transactional and reference tables, including WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER for optimistic locking. A SECURITY_GROUP_ID column is present for multi-organization / data security filtering, and ORIG_SYSTEM_ID with ORIG_SYSTEM_REF supports the TCA origin system mechanism for identifying records from external feeder systems.

Based on the heuristic Data Vault classification mined from its foreign key structure, this object is best modeled as satellite-leaning. It describes attributes of a location area rather than acting as an independent hub or a many-to-many link. This classification is a modeling suggestion and does not affect its operational role in EBS.

Key Information Stored

The table contains 15 documented physical columns. The most significant are summarized below.

• LOCATION_POSTAL_CODE_ID — the surrogate primary key, backed by the unique index JTF_LOC_POSTAL_CODES_U1. This index is a business-key candidate in the sense that it uniquely identifies each row, though the column is a system-generated identifier.
• LOCATION_AREA_ID — foreign key to JTF_LOC_AREAS_B, the parent location area to which the postal code range belongs. This is the dominant join column for consumers of the table.
• POSTAL_CODE_START and POSTAL_CODE_END — the range boundaries. Together these define the postal code interval associated with the location area.
• START_DATE_ACTIVE and END_DATE_ACTIVE — effective-dating columns controlling the validity window of the range assignment.
• ORIG_SYSTEM_ID — foreign key to HZ_ORIG_SYSTEMS_B, identifying the source system for the record.
• ORIG_SYSTEM_REF — the external reference value within the originating system.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, used for data segregation.
• OBJECT_VERSION_NUMBER — concurrency control for the OAF/BC4J layer.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — standard audit (WHO) columns.

Common Use Cases and Queries

The primary use case is resolving which location area corresponds to a given postal code, or conversely enumerating all postal code ranges for an area. A common pattern is:

• Retrieve all active ranges for a location area: SELECT POSTAL_CODE_START, POSTAL_CODE_END FROM JTF_LOC_POSTAL_CODES WHERE LOCATION_AREA_ID = :area_id AND TRUNC(SYSDATE) BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)).
• Reverse lookup by postal code, joining to JTF_LOC_AREAS_B: SELECT a.AREA_NAME FROM JTF_LOC_POSTAL_CODES p, JTF_LOC_AREAS_B a WHERE p.LOCATION_AREA_ID = a.LOCATION_AREA_ID AND :postal_code BETWEEN p.POSTAL_CODE_START AND p.POSTAL_CODE_END.
• Reporting on geographic coverage by area, or auditing records sourced from an external system using ORIG_SYSTEM_ID and ORIG_SYSTEM_REF.
• Data cleanup and reconciliation of ranges against the HZ location tables.

Because ranges may overlap in poorly maintained data, queries used for territory assignment should include effective-date filters and, where necessary, tie-break logic.

Related Objects

• JTF_LOC_AREAS_B — referenced via LOCATION_AREA_ID; the base table for the location area owning the postal code range.
• HZ_ORIG_SYSTEMS_B — referenced via ORIG_SYSTEM_ID; identifies the external source system.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; governs row-level security.
• JTF_LOC_AREAS_TL — translated area names joined by LOCATION_AREA_ID for reporting.
• HZ_LOCATIONSHZ_LOCATION_PROFILES — TCA location and geography tables frequently combined with area data.

These relationships make JTF_LOC_POSTAL_CODES a satellite of the location area entity, supporting geographic range maintenance and territory-based processing throughout the EBS CRM stack.