Search Results per_grades_vl




Overview
Explore in AI Chat

Overview

PER_GRADES_VL is a Multi-Language (ML) style database view owned by the APPS schema within the Human Resources (PER) product module. It persists in a VALID and compilable state in both Oracle E-Business Suite 12.1.1 and 12.2.2. The view presents a translatable, language-aware projection of grade definitions, merging the language-independent grade record stored in PER_GRADES with the language-dependent grade name stored in PER_GRADES_TL. The "VL" suffix denotes a "View Language" construct: rather than exposing all translation rows, the view filters underlying translation data to the session's current language, resolving it dynamically through USERENV('LANG').

In EBS reporting and integration scenarios, PER_GRADES_VL serves as the standard entry point for retrieving a grade's business attributes together with its user-facing, translated NAME in a single query. It is commonly consumed by concurrent programs, Oracle Reports, BI Publisher templates, OAF pages, and external integrations that must display or validate grades without executing manual joins to the translation table.

Underlying Base Objects

As documented in ETRM 12.2.2 (and consistent with 12.1.1), the view is defined over two referenced base objects, both exposed to APPS as synonyms:

  • PER_GRADES (SYNONYM) — The language-independent grade entity, aliased as B in the view definition. It holds GRADE_ID, BUSINESS_GROUP_ID, GRADE_DEFINITION_ID, effective dates, sequence, comments, descriptive flexfield (DFF) columns, WHO columns, and OBJECT_VERSION_NUMBER.
  • PER_GRADES_TL (SYNONYM) — The translation table, aliased as T, which stores the language-specific NAME keyed by GRADE_ID and LANGUAGE.

The view joins these on GRADE_ID and constrains T.LANGUAGE to USERENV('LANG'), yielding one row per grade in the caller's language. The primary key GRADE_ID is inherited from PER_GRADES, and the translation join supplies the NAME column that appears in the view's column list.

Key Columns

The view exposes the full column set of PER_GRADES plus the translated NAME. Significant columns include:

  • ROW_ID — Physical row identifier from PER_GRADES, useful for direct row access.
  • GRADE_ID — Primary key and principal foreign key referenced by assignment and grade-rate entities.
  • BUSINESS_GROUP_ID — Owning business group, central to HR security and multi-organization filtering.
  • GRADE_DEFINITION_ID — Links the grade to its grade definition (valid grades, progression rules).
  • DATE_FROM / DATE_TO — Effective date boundaries controlling the grade's active period.
  • SEQUENCE — Ordering value used for grade ranking.
  • NAME — The translated grade name sourced from PER_GRADES_TL in the session language.
  • ATTRIBUTE1–ATTRIBUTE20, ATTRIBUTE_CATEGORY — Descriptive flexfield storage.
  • INFORMATION1–INFORMATION20, INFORMATION_CATEGORY — Additional flexfield information columns.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit context.
  • WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER — Standard audit and optimistic locking attributes.

Common Use Cases and Queries

Typical uses include listing all grades for a business group in the user's language, resolving NAME for a known GRADE_ID, and feeding grade data into assignment or compensation reports.

Retrieve all current grades for a business group:

SELECT grade_id, name, date_from, date_to, sequence
FROM apps.per_grades_vl
WHERE business_group_id = :p_bg_id
ORDER BY sequence;

Resolve a grade name for a specific identifier:

SELECT name FROM apps.per_grades_vl WHERE grade_id = :p_grade_id;

Join to assignments for reporting:

SELECT g.name grade_name, a.assignment_id
FROM apps.per_grades_vl g, apps.per_all_assignments_f a
WHERE a.grade_id = g.grade_id
AND :p_effective_date BETWEEN a.effective_start_date AND a.effective_end_date;

Because the view filters on USERENV('LANG'), callers should ensure the session language is set appropriately; when no translation exists in the target language, the join produces no NAME row, so integrations requiring a fallback should query PER_GRADES_TL directly with an explicit language predicate.