Search Results per_grades_tl_n1

Overview

HR.PER_GRADES_TL is the translation-enabled ("TL") child table that stores language-specific descriptive attributes for grade definitions in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is owned by the HR schema and registered under FND Design Data as PER.PER_GRADES_TL. The physical table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, and its indexes are built in APPS_TS_TX_IDX. The table exists to hold the translated grade name text for each grade record, keyed by language, so that the same underlying grade can present a caregiver-facing label in any installed or defined language.

From a Data Vault modeling perspective, the ETRM relationship metadata classifies this object heuristically as standalone. In practice it behaves as a satellite attached to the grade entity: GRADE_ID is the foreign key back to per_grades, LANGUAGE supplies the multi-active context, and NAME carries the descriptive payload. Analysts should treat it as a language-scoped descriptive satellite rather than an independent hub or link.

Key Information Stored

The table documents nine columns. The most significant are:

• GRADE_ID (NUMBER(15)) — the foreign key to per_grades. Together with LANGUAGE it forms the surrogate/business composite that identifies a row.
• LANGUAGE (VARCHAR2) — the defined language of the translated row.
• SOURCE_LANG (VARCHAR2) — the actual language from which the row was derived, used by the translation framework to detect stale translations.
• NAME (VARCHAR2(240)) — the grade name; this is the primary business-meaningful payload and the field most commonly exposed in reports.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Who columns captured automatically by the Oracle Forms/concurrent infrastructure.

The primary key is PER_GRADES_TL_PK, a unique index on (GRADE_ID, LANGUAGE); this is the canonical business-key candidate and uniquely identifies each translated row. A secondary non-unique index, PER_GRADES_TL_N1, covers (LANGUAGE, NAME) to accelerate lookup by translated name. Note that no separate surrogate sequence column is documented; GRADE_ID is inherited from the parent grade record.

Common Use Cases and Queries

The most frequent reporting requirement is to retrieve the grade name for a given grade in a specified language, joining on GRADE_ID while restricting to the caller's session language. A typical pattern is:

• SELECT g.grade_id, t.name FROM hr.per_grades g, hr.per_grades_tl t WHERE g.grade_id = t.grade_id AND t.language = USERENV('LANG') — a joined lookup for the localized label.
• SELECT grade_id, language, source_lang, name FROM hr.per_grades_tl WHERE grade_id = :p_grade_id — retrieve all translations for a grade.
• Comparing SOURCE_LANG to LANGUAGE to flag rows that may be stale and require re-translation.
• Driving value-set or DFF validation queries where the grade label must match the reporting language.

The PER_GRADES_TL_N1 index makes searches by NAME efficient when the language is known, which is useful for ad-hoc HR reporting and for confirming translation coverage.

Related Objects

The metadata indicates PER_GRADES_TL does not reference any database object, but it is referenced by the view PER_GRADES_TL# (the underlying object exposed to the translation/MLS framework). The following related objects are the most significant for joins and lookups:

• HR.PER_GRADES — parent grade definition table; join on PER_GRADES_TL.GRADE_ID = PER_GRADES.GRADE_ID.
• HR.PER_GRADES_TL# — the MLS view wrapping this table, referenced by the translation infrastructure.
• HR.PER_GRADES_TL_PK — unique index (GRADE_ID, LANGUAGE) enforcing the natural key.
• HR.PER_GRADES_TL_N1 — non-unique index (LANGUAGE, NAME) supporting name-based retrieval.
• Grade-related value sets and DFFs that consume HR.PER_GRADES, and hence its translations, for validation and display.

When reporting, always join through PER_GRADES rather than querying the TL table alone, since grade identity and status are held on the parent.