Search Results per_jp_postal_codes_pk




Overview
Explore in AI Chat

Overview

HR.PER_JP_POSTAL_CODES is a reference data table within the Oracle E-Business Suite HR schema that stores Japanese postal code information sourced from the Ministry of Posts and Telecommunications. In the context of Oracle EBS 12.1.1 and 12.2.2, this table supports the Japanese localization of the person and address infrastructure, enabling the application to resolve postal codes into region names expressed in both Kanji and Kana characters. It is registered in FND Design Data as PER.PER_JP_POSTAL_CODES, resides in the APPS_TS_TX_DATA tablespace, and is classified as VALID in the ETRM repository.

From a Data Vault modeling perspective, the mined metadata suggests a satellite-leaning classification. The table holds descriptive attributes (region names and their Kana equivalents) that qualify the business key formed by postal code and district code, rather than acting as an independent hub of core business entities. This classification is a heuristic suggestion derived from the foreign key structure and should be treated as a modeling guide rather than a binding architectural statement.

Key Information Stored

The table contains nine documented columns. The most significant are:

  • POSTAL_CODE (VARCHAR2, 10) — the primary postal identifier; part of the composite primary key.
  • DISTRICT_CODE (VARCHAR2) — the Japanese district code, explicitly described as the column the user searched for; it is the second component of the composite primary key and the target of a foreign key reference to PER_JP_ADDRESS_LOOKUPS.
  • REGION (VARCHAR2, 150) — the Japanese region name rendered in Kanji characters.
  • REGION_KANA (VARCHAR2, 250) — the same region name rendered in Kana characters, supporting phonetic sorting and search.
  • Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE, which provide audit traceability consistent with EBS conventions.

The surrogate-style uniqueness of this reference object is expressed through the business key candidate PER_JP_POSTAL_CODES_PK, a UNIQUE normal index defined over POSTAL_CODE and DISTRICT_CODE. A second index, PER_JP_POSTAL_CODES_N1, is a NONUNIQUE index on DISTRICT_CODE alone, optimizing lookups that filter by district.

Common Use Cases and Queries

Typical usage centers on address entry and validation for Japanese employees, applicants, and other person records. Applications resolve a postal code to its region, or filter postal records by district. A representative query is:

  • SELECT POSTAL_CODE, DISTRICT_CODE, REGION, REGION_KANA FROM HR.PER_JP_POSTAL_CODES WHERE POSTAL_CODE = :p_postal_code;
  • SELECT POSTAL_CODE, REGION FROM HR.PER_JP_POSTAL_CODES WHERE DISTRICT_CODE = :p_district_code; — leverages the PER_JP_POSTAL_CODES_N1 index for district-driven lookups.
  • Reporting on region name pairs in Kanji and Kana for localization or validation of address master data.

Because the table is reference data rather than transactional data, queries are typically read-only joins against address and lookup tables during form validation or conversion routines.

Related Objects

The documented foreign key relationship links this table to HR.PER_JP_ADDRESS_LOOKUPS via the DISTRICT_CODE column. Reference is also made to PER_JP_POSTAL_CODES# as a dependent object (the backing synonym/view traditionally used for this localization table). Related objects in the broader person/address model include PER_JP_ADDRESS_LOOKUPS (the foreign-key parent for district codes) and the general address tables within the HR schema. Users should also consult the standard Who columns and the FND Design Data registration PER.PER_JP_POSTAL_CODES when tracing dependencies. Because the ETRM excerpt documents only one explicit foreign key and a single dependent object, additional relationships should be verified directly against the dictionary for a specific EBS patch level.