Search Results hier_version_status_code

Overview

HRIFV_US_FEDREP_GEN_HRCHY is a reporting view owned by the APPS schema within the HRI (Human Resources Intelligence) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to expose the flattened structure of General Hierarchy definitions, their versions, and their nodes in a form suitable for U.S. federal reporting and downstream HR intelligence extracts. The view joins the hierarchy summary fact-style object with the underlying General Hierarchy definition, version, and node tables, and enriches each row with decoded lookup values, organization unit names, and location address details.

The view is primarily consumed by HRMS intelligence reports and BIS-based extracts that require a denormalized, human-readable representation of hierarchy metadata. Because it performs the lookup decoding inline through the HR_BIS package, report developers do not need to re-implement the decode logic for hierarchy types, version statuses, or node types.

Underlying Base Objects

The view is defined over the following documented base objects:

• HRI_GEN_HRCHY_SUMMARY — the driving summary object that provides the hierarchy, version, node, and entity identifiers.
• PER_GEN_HIERARCHY — the General Hierarchy definition, supplying NAME and TYPE.
• PER_GEN_HIERARCHY_VERSIONS — the version records, supplying VERSION_NUMBER, DATE_FROM, DATE_TO, and STATUS.
• PER_GEN_HIERARCHY_NODES — the node records referenced through the summary.
• HR_LOCATIONS_ALL, HR_LOCATIONS_ALL_TL, HR_LOCATION_EXTRA_INFO — location and address attributes used to build the sub-node name and long name.
• HR_ALL_ORGANIZATION_UNITS_TL and HR_ORGANIZATION_INFORMATION — organization unit name sources for the node name columns.
• HR_BIS and HR_GENERAL — PL/SQL packages supporting lookup decoding and the END_OF_TIME constant.

The view therefore aggregates hierarchy definition, versioning, node, organization, and location data into a single denormalized result set scoped by business group.

Key Columns

• HIERARCHY_NAME — the name of the general hierarchy, taken from PER_GEN_HIERARCHY.NAME.
• HIERARCHY_TYPE — a decoded, display-ready value of the hierarchy type, derived via HR_BIS.BIS_DECODE_LOOKUP('HIERARCHY_TYPE', PGH.TYPE).
• HIERARCHY_TYPE_CODE — the raw numeric type code from PER_GEN_HIERARCHY.TYPE.
• HIERARCHY_VERSION_NUMBER, HIERARCHY_VERSION_DATE_FROM, HIERARCHY_VERSION_DATE_TO — versioning attributes of the hierarchy.
• HIER_VERSION_DATE_TO_NN — a non-null version end date substituting HR_GENERAL.END_OF_TIME.
• HIER_VERSION_STATUS_CODE and HIER_VERSION_STATUS — the version status code and its decoded active/inactive description.
• ENTITY_LEVEL, NODE_TYPE_CODE, NODE_TYPE — node classification and decoded node type.
• NODE_NAME and NODE_LONG_NAME — organization unit names for the node.
• SUB_ENTITY_LEVEL, SUB_NODE_TYPE_CODE, SUB_NODE_TYPE — sub-node classification and its decoded type.
• SUB_NODE_NAME, SUB_NODE_LONG_NAME — location code and concatenated address components for the sub-node.
• HIERARCHY_ID, HIERARCHY_VERSION_ID, BUSINESS_GROUP_ID, ENTITY_ID, HIERARCHY_NODE_ID, and the SUB_* identifiers — surrogate keys supporting joins and drill-through.

Common Use Cases and Queries

The view is typically queried when a report must list general hierarchies by type and version, or when node and location detail is required for a federal-style hierarchy extract. A simple filtered query on hierarchy type is shown below; note that the user’s search term "hierarchy_type" corresponds directly to the HIERARCHY_TYPE column.

SELECT hierarchy_name, hierarchy_type, hierarchy_version_number,
       node_name, node_type, sub_node_name
  FROM apps.hrifv_us_fedrep_gen_hrchy
 WHERE hierarchy_type = :p_hierarchy_type;

Because version date ranges are exposed, a point-in-time query can be constructed using HIERARCHY_VERSION_DATE_FROM and HIER_VERSION_DATE_TO_NN to retrieve only the version effective on a given date. The BUSINESS_GROUP_ID column supports multi-organization filtering. Referenced flexfield descriptors are also exposed as literal column markers for descriptive flexfield purposes, allowing report definitions to reuse the same DT-style mapping conventions used elsewhere in HRMS intelligence reporting.

Developers extending this view should remember that it is a VIEW owned by APPS and that its columns are read-only; inserts and updates must target the underlying base tables. Access should be granted carefully, since the view spans organization and location data.