Results for “edw_hr_grd_grades_ltc”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The HRI.EDW_HR_GRD_GRADES_LTC table is an Oracle Human Resources Intelligence (HRI) data object. It acts as a denormalized extraction/mart table within the EBS HR analytics warehouse, holding grade-level reference data that has been flattened from the operational PER_GRADE_DEFINITIONS base table and its related grade structures. The suffix _LTC and the columns prefixed with ALL_FK suggest this is a "flattened/denormalized key" version of the grade dimension, materialized for efficient loading and reporting by Oracle HRMS Intelligence (formerly Daily Business Intelligence / HR Analytics) components.

Based on the FK pattern (a single foreign key from GRADE_DEFINITION_ID to PER_GRADE_DEFINITIONS) and the presence of independent unique/primary key columns (GRADE_PK, GRADE_PK_KEY), the mined Data Vault classification places this object as a satellite (or a grade hub-satellite) of the operational grade definition. This classification is a heuristic modeling suggestion; it indicates the table carries descriptive attributes keyed to a grade business key rather than acting as a many-to-many link between two independent business concepts.

Key Information Stored

The table comprises 21 documented columns. The most significant are:

• GRADE_DEFINITION_ID — the documented foreign key linking to PER_GRADE_DEFINITIONS, tying each row back to the source grade definition.
• GRADE_PK and GRADE_PK_KEY — surrogate/internal key columns; both are documented as business-key candidates via unique indexes EDW_HR_GRD_GRADES_LTC_U1 (on GRADE_PK_KEY) and EDW_HR_GRD_GRADES_LTC_U2 (on GRADE_PK, GRADE_PK_KEY).
• GRADE_ID — the operational grade identifier carried through from the source system.
• NAME — the grade name as presented to end users.
• GRADE_DP — the display/derived grade description attribute.
• GRADE_DATE_FROM and GRADE_DATE_TO — the effective dating range for the grade record, supporting date-effective reporting.
• BUSINESS_GROUP and BUSINESS_GROUP_ID — the business group (enterprise) context scoping the grade.
• GRADE_SEQUENCE — the ordinal sequence used to order grades (for ranking or reporting sort).
• ALL_FK and ALL_FK_KEY — flattened FK key columns used in the warehouse transformation/denormalization layer.
• LAST_UPDATE_DATE and CREATION_DATE — standard EBS audit timestamps supporting incremental extraction and change detection.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE5 — the standard EBS descriptive flexfield (DFF) attribute columns, carried through for custom segment reporting.
• INSTANCE — identifies the source EBS instance in multi-instance warehouse loads.

Common Use Cases and Queries

This table is primarily used by HR analytics dashboards, grade-distribution reports, headcount-by-grade analyses, and grade-level comparisons across business groups. A typical BI query selects grade attributes joined to the operational definition:

SELECT g.GRADE_ID, g.NAME, g.GRADE_SEQUENCE, g.GRADE_DATE_FROM, g.GRADE_DATE_TO, p.NAME
FROM HRI.EDW_HR_GRD_GRADES_LTC g
JOIN PER_GRADE_DEFINITIONS p ON p.GRADE_DEFINITION_ID = g.GRADE_DEFINITION_ID
WHERE g.BUSINESS_GROUP_ID = :p_bg_id
ORDER BY g.GRADE_SEQUENCE;

Incremental ETL patterns filter on LAST_UPDATE_DATE > :last_run to load only changed grade rows. Grade-history reports filter on GRADE_DATE_FROM / GRADE_DATE_TO. DFF-based analytics use USER_ATTRIBUTE1–USER_ATTRIBUTE5 for custom segment slicing. Instance-aware consolidations filter on INSTANCE to isolate a source EBS platform.

Related Objects

The most significant related objects include:

• PER_GRADE_DEFINITIONS — the primary referenced table, joined on GRADE_DEFINITION_ID.
• PER_GRADES and PER_GRADE_STEPS — operational grade and step definitions supplying the underlying hierarchy.
• PER_ALL_ASSIGNMENTS_F — assignments reference grades via GRADE_ID; used to compute headcount by grade.
• PER_GRADE_RATES — pay rate information keyed to grades.
• Other HRI EDW dimension tables (e.g., EDW_HR_GRD_* siblings) that share the GRADE_PK key for cross-dimensional joins.
• HRMS Intelligence reporting views (e.g., grade-level DBI/HRI report views) that consume this table as the conformed grade dimension.