Search Results per_jp_school_lookups_v




Overview
Explore in AI Chat

Overview

PER_JP_SCHOOL_LOOKUPS_V is a database view owned by the APPS schema in Oracle E-Business Suite, registered as VALID and delivered under the PER (Human Resources) product family. It exposes Japanese school information used across Oracle HRMS for the localization of Japanese employee and applicant records. The view presents school lookup data such as school identifiers, school names in both Kanji and Kana, and major (field of study) descriptors, allowing Oracle Forms, concurrent programs, and reporting tools to retrieve the reference data required by the Japanese statutory school master.

In EBS 12.1.1 and 12.2.2 the view behaves as a thin projection layer over a single synonym. It does not perform joins, aggregations, or filtering beyond the string decomposition embedded in its SELECT list, so it is inexpensive to query and suitable for both online validation and batch extraction. The view is part of the country-specific localization content that supports the reporting of educational background for Japanese employees, a mandatory element of several Japanese HR and payroll statutory reports.

Underlying Base Objects

The documented metadata identifies exactly one referenced base object: the synonym PER_JP_SCHOOL_LOOKUPS, which resolves to the base school lookup table in the APPS schema. Because the view is defined over a synonym rather than a fully qualified table name, the resolution depends on the standard EBS synonym configuration shipped with the applications installation. The view text selects directly from PER_JP_SCHOOL_LOOKUPS with no WHERE clause, meaning every row of the base table is exposed.

The projection applies two derivations. First, ROWIDTOCHAR(ROWID) is aliased as ROW_ID to supply a stable, character-typed row identifier. Second, the SCHOOL_ID is split into three byte-oriented substrings using SUBSTRB: the first component covers bytes 1 through 2 (SCHOOL_TYPE), bytes 3 through 6 (SCHOOL_CODE), and bytes 9 through 3 (MAJOR_CODE). All remaining columns are passed through unchanged. Because the decomposition is positional and byte-based, the SCHOOL_ID format must be respected by any data loaded into the base table; the view has no inherent validation.

Key Columns

  • ROW_ID – Character representation of the base row's ROWID, produced by ROWIDTOCHAR.
  • SCHOOL_ID – The composite school identifier from which the type, code, and major components are derived.
  • SCHOOL_TYPE – SUBSTRB(SCHOOL_ID, 1, 2); the two-byte code classifying the institution.
  • SCHOOL_CODE – SUBSTRB(SCHOOL_ID, 3, 6); the six-byte school code segment.
  • SCHOOL_NAME – The school name as stored, typically in Japanese characters.
  • SCHOOL_NAME_KANA – The phonetic Kana rendering of the school name.
  • MAJOR_CODE – SUBSTRB(SCHOOL_ID, 9, 3); the three-byte major or field-of-study code segment.
  • MAJOR and MAJOR_KANA – The major description and its Kana equivalent.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – Standard EBS audit columns inherited from the base table.

Common Use Cases and Queries

The view is most commonly queried to validate or display a school selected on a Japanese employee or applicant record, or to populate LOV-enabled fields with Kana-aware search values. Because SCHOOL_ID is the primary search key for the user's query, the typical access path filters on that column or on the derived SCHOOL_CODE segment.

Retrieving a school by identifier:

  • SELECT school_id, school_name, school_name_kana, major, major_kana FROM apps.per_jp_school_lookups_v WHERE school_id = :p_school_id;

Searching by school code or type when only the decomposed segment is known:

  • SELECT school_id, school_code, school_type, school_name FROM apps.per_jp_school_lookups_v WHERE school_code = '123456' ORDER BY school_name_kana;

Kana-ordered extraction for reporting:

  • SELECT school_id, school_name, school_name_kana FROM apps.per_jp_school_lookups_v ORDER BY school_name_kana;

These patterns support LOV validation, data migration verification, and localization extract programs. Because the view is a direct projection of a single base object with no joins, it carries no additional performance overhead beyond the base table scan and is safe to reference from custom concurrent programs and BI Publisher data models.