Search Results per_jp_postal_codes




Overview
Explore in AI Chat

Overview

PER_JP_POSTAL_CODES is a Human Resources (PER) module table residing in the HR schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as a localized reference table that stores Japanese postal code and district information, enabling address validation, defaulting, and formatting for Japanese employees, applicants, and other person records managed through Oracle HRMS. Unlike transactional tables that capture business events, PER_JP_POSTAL_CODES is a static lookup maintained to support the Japanese localization requirements of the PER product family.

Within the ETRM 12.2.2 documented schema, the table exposes nine columns and is classified, via heuristic Data Vault modeling applied to its foreign key structure, as satellite-leaning. This classification suggests the table is best modeled as a descriptive satellite attached to a parent lookup entity (PER_JP_ADDRESS_LOOKUPS), rather than as a standalone hub or link. The designation reflects that PER_JP_POSTAL_CODES carries descriptive attributes (region and kana information) keyed to a district reference, and does not itself represent an independent business entity or a many-to-many association between hubs.

Key Information Stored

The table stores postal code lookups for Japan, combining a postal code with a district designation. The most significant columns documented are:

  • POSTAL_CODE — The Japanese postal code value forming part of the composite primary key.
  • DISTRICT_CODE — The district identifier, also part of the composite primary key and the foreign key column referencing PER_JP_ADDRESS_LOOKUPS.
  • REGION — The region name associated with the postal code and district combination.
  • REGION_KANA — The phonetic kana rendering of the region, supporting search and sorting by Japanese syllabary.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing the last modification timestamp, user, and login session.
  • CREATED_BY, CREATION_DATE — Standard WHO audit columns recording the creating user and creation timestamp.

The surrogate primary key is not a single column but a composite business key: PER_JP_POSTAL_CODES_PK, defined over (POSTAL_CODE, DISTRICT_CODE). No separate system-generated surrogate identifier is documented. The unique index PER_JP_POSTAL_CODES_PK serves as the business-key candidate, guaranteeing that each postal code/district pairing is unique. The foreign key from DISTRICT_CODE to PER_JP_ADDRESS_LOOKUPS establishes the parent-child relationship underpinning the satellite classification.

Common Use Cases and Queries

Typical scenarios include validating employee address entries against known Japanese postal codes, populating region fields during address capture, and driving localization reports for HR and payroll. A common lookup retrieves region details for a given postal code:

  • SELECT postal_code, district_code, region, region_kana FROM hr.per_jp_postal_codes WHERE postal_code = :p_postal_code;
  • SELECT p.postal_code, p.region, p.region_kana, a.district_name FROM hr.per_jp_postal_codes p, hr.per_jp_address_lookups a WHERE p.district_code = a.district_code AND p.postal_code LIKE :p_prefix || '%';
  • Reporting on district coverage to reconcile localized address data against PER_JP_ADDRESS_LOOKUPS.
  • Sorting address lists by REGION_KANA to present entries in phonetic order for Japanese users.

Related Objects

PER_JP_POSTAL_CODES is tightly coupled to the following objects in the PER/HR localization data model:

  • PER_JP_ADDRESS_LOOKUPS — The parent lookup table referenced by DISTRICT_CODE; the foreign key relationship defines the satellite-link between these objects.
  • PER_JP_ADDRESS_LOOKUPS.DISTRICT_CODE — The referenced key column forming the join predicate.
  • PER_JP_POSTAL_CODES_PK — The composite unique index (POSTAL_CODE, DISTRICT_CODE) enforcing business-key uniqueness.
  • PER_ADDRESSES — Stores person addresses that may be validated or defaulted using postal code and district values from this table.
  • PER_PEOPLE_F — The person master referencing addresses whose geographic attributes depend on the localized lookup chain.

Together these objects support the Japanese address localization feature set within Oracle HRMS, with PER_JP_POSTAL_CODES acting as the descriptive satellite supplying region and kana detail keyed to district lookups.