Search Results hier_version_status_code

Overview

The view APPS.HRIFV_US_FEDREP_GEN_HRCHY is a reporting and integration object within the Oracle E-Business Suite Human Resources (HR) module. It is documented as part of the FND design data under the product short name HRI (Oracle HRMS Intelligence / HR Reporting) and carries a status of VALID in both the 12.1.1 and 12.2.2 releases. The view presents flattened, denormalized data describing generic hierarchies and their associated hierarchy versions and nodes, with a specific orientation toward United States federal reporting requirements, as indicated by the US_FEDREP component of its name.

The central purpose of this view is to expose hierarchy version metadata — including version number, effective dates, and status — alongside the entity and node structure that those versions describe. Because the view joins hierarchy summaries to organization, location, and node detail sources, it allows consumers to resolve a hierarchy version's HIER_VERSION_STATUS_CODE and its corresponding translated status value without writing complex multi-table joins against the underlying transactional tables.

Per Oracle's standard warning on internal objects, this view is designated Oracle Internal Use Only, and Oracle Corporation does not support direct access to Oracle applications data through it except from standard Oracle Applications programs. Developers and integrators constructing custom reports should therefore treat the view as a convenience read-only projection whose column set and semantics may change between release levels.

Underlying Base Objects

The view is defined over a mixture of synonyms and PL/SQL packages. The documented base objects are:

• PER_GEN_HIERARCHY, PER_GEN_HIERARCHY_VERSIONS, and PER_GEN_HIERARCHY_NODES — the primary hierarchy definition, versioning, and node tables in the PER (Human Resources / People) product.
• HRI_GEN_HRCHY_SUMMARY — a pre-aggregated summary synonym that supplies hierarchy-level descriptors.
• HR_ALL_ORGANIZATION_UNITS_TL — translated organization unit names and types.
• HR_LOCATIONS_ALL, HR_LOCATIONS_ALL_TL, and HR_LOCATION_EXTRA_INFO — location and location-attribute sources.
• HR_ORGANIZATION_INFORMATION — supplementary organization-level attributes.
• HR_BIS and HR_GENERAL — PL/SQL packages, used to derive translated or formatted display values such as the descriptive status and type text.

The presence of the package references explains why several descriptive columns (for example HIER_VERSION_STATUS and NODE_TYPE) carry a length of 4000 — they are computed values rather than stored column lookups.

Key Columns

• HIER_VERSION_STATUS_CODE (VARCHAR2, 30) — the coded status of the hierarchy version, such as active, inactive, or pending. This is the column most directly related to the search term.
• HIER_VERSION_STATUS (VARCHAR2, 4000) — the translated or formatted descriptive status derived from the code via the HR packages.
• HIERARCHY_NAME, HIERARCHY_TYPE, HIERARCHY_TYPE_CODE — identify the hierarchy and classify it, with both coded and descriptive forms.
• HIERARCHY_VERSION_NUMBER, HIERARCHY_VERSION_DATE_FROM, HIERARCHY_VERSION_DATE_TO — the version ordinal and its effective date window.
• HIER_VERSION_DATE_TO_NN — a non-null variant of the version end date, typically used to support range comparisons where a null end date would otherwise be problematic.
• ENTITY_LEVEL, ENTITY_ID, NODE_TYPE_CODE, NODE_NAME, NODE_LONG_NAME — position and identity of the node within the hierarchy.
• SUB_ENTITY_LEVEL, SUB_NODE_TYPE_CODE, SUB_NODE_NAME, SUB_NODE_LONG_NAME, SUB_ENTITY_ID — the junior node or sub-entity associated with the row.
• Surrogate keys: HIERARCHY_ID, HIERARCHY_VERSION_ID, HIERARCHY_NODE_ID, SUB_HIERARCHY_NODE_ID, BUSINESS_GROUP_ID, SUB_ENTITY_BG_ID.
• Hidden _DF: columns — internal design-data markers that should be excluded from user queries.

Common Use Cases and Queries

Typical scenarios involve determining which hierarchy versions are currently active, auditing version history by date, and producing federal reporting extracts that require organization and location context. The following query returns active hierarchy versions and their nodes.

SELECT hierarchy_name, hierarchy_version_number, hier_version_status_code, hier_version_status, node_name, node_long_name FROM apps.hrifv_us_fedrep_gen_hrchy WHERE hier_version_status_code = 'ACTIVE' AND business_group_id = :p_bg_id ORDER BY hierarchy_name, hierarchy_version_number, entity_level;

To identify versions in force on a given date, the non-null end-date column is used for symmetrical comparison:

SELECT hierarchy_id, hierarchy_version_id, hierarchy_version_number, hierarchy_version_date_from, hier_version_date_to_nn FROM apps.hrifv_us_fedrep_gen_hrchy WHERE TRUNC(:p_effective_date) BETWEEN hierarchy_version_date_from AND hier_version_date_to_nn;

When querying this view, exclude the _DF: design-data columns, filter by BUSINESS_GROUP_ID to respect multi-organization security, and rely on the coded columns for logic while using the 4000-length descriptive columns only for display.