Results for “organization_hierarchy_version”

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

Overview

HRFV_ORGANIZATION_HIERARCHIES is an APPS-owned view in the Oracle E-Business Suite PER (Human Resources) product, classified as a business view template from which a flexfield view is generated. It presents the organizational hierarchy of an enterprise as a flattened, query-ready result set, combining values from the organization hierarchy definition, its versions, and the descriptive name of each organization unit. The view is owned by APPS and is documented as VALID in both EBS 12.1.1 and 12.2.2. Because it resolves organization names through language-sensitive translation tables, it returns names in the session language. The view is intended primarily for reporting and integration scenarios in which hierarchy structures must be presented in a denormalized form rather than navigated recursively.

Underlying Base Objects

The view is defined over the following documented base objects:

• HRBV_ORGANIZATION_DESCENDENTS (VIEW) — supplies the hierarchical rows, including ORG_LEVEL, ORGANIZATION_ID, PARENT_ORGANIZATION_ID, BUSINESS_GROUP_ID, and ORG_STRUCTURE_VERSION_ID, acting as the horizontal source of the hierarchy.
• HR_ALL_ORGANIZATION_UNITS_TL (SYNONYM) — joined three times as ORG1T, ORG2T, and BGRT to resolve the child organization name, parent organization name, and business group name, each filtered by LANGUAGE = USERENV('LANG').
• PER_ORGANIZATION_STRUCTURES (SYNONYM) — provides the hierarchy definition, including ORGANIZATION_STRUCTURE_ID, the structure name, and PRIMARY_STRUCTURE_FLAG.
• PER_ORG_STRUCTURE_VERSIONS (SYNONYM) — provides version details: DATE_FROM, DATE_TO, VERSION_NUMBER, and ORG_STRUCTURE_VERSION_ID.
• HR_BIS (PACKAGE) — supplies BIS_DECODE_LOOKUP for the YES_NO decoding of the primary hierarchy flag and GET_SEC_PROFILE_BG_ID, which restricts rows to the business group of the current security profile.
• PER_VIEWS_PKG (PACKAGE) — provides PER_GET_ORGANIZATION_EMPLOYEES, the scalar call returning the employee count for each organization.

Joins are performed on ORG_STRUCTURE_VERSION_ID, ORGANIZATION_STRUCTURE_ID, ORGANIZATION_ID, PARENT_ORGANIZATION_ID, and BUSINESS_GROUP_ID, and the view is defined WITH READ ONLY.

Key Columns

• BUSINESS_GROUP_NAME — name of the business group owning the hierarchy.
• CHILD_ORGANIZATION_NAME — the organization unit name, indented with RPAD of a hyphen repeated to the hierarchy level.
• PARENT_ORGANIZATION_NAME — the name of the immediate parent organization.
• ORGANIZATION_HIERARCHY_NAME — the name of the organization structure (hierarchy).
• PRIMARY_HIERARCHY_FLAG — decoded Yes/No indicator of the primary structure flag.
• ORGANIZATION_HIERARCHY_LEVEL — the depth of the organization within the hierarchy (ORG_LEVEL).
• NUMBER_OF_EMPLOYEES — employee count for the organization, from PER_GET_ORGANIZATION_EMPLOYEES.
• HIERARCHY_VERSION_START_DATE / HIERARCHY_VERSION_END_DATE — validity window of the hierarchy version.
• ORGANIZATION_HIERARCHY_VERSION — the version number of the structure.
• BUSINESS_GROUP_ID — identifier of the business group.
• CHILD_ORGANIZATION_ID — identifier of the organization unit at the current hierarchy level. This is the column matched by the search term "child_organization_id" and is the key used to join the hierarchy back to HR_ALL_ORGANIZATION_UNITS or to any organization-keyed table.
• ORGANIZATION_HIERARCHY_ID — identifier of the organization structure.
• ORG_HIERARCHY_VERSION_ID — identifier of the structure version.
• PARENT_ORGANIZATION_ID — identifier of the immediate parent organization.

Common Use Cases and Queries

Typical uses include hierarchy reports, reconciliation of organization trees, and integration extracts that require a denormalized parent/child listing with employee counts. Because the view joins translation tables, no additional language join is required for names.

Retrieve the full hierarchy for a given business group:

• SELECT ORGANIZATION_HIERARCHY_NAME, ORGANIZATION_HIERARCHY_LEVEL, CHILD_ORGANIZATION_NAME, PARENT_ORGANIZATION_NAME, NUMBER_OF_EMPLOYEES FROM APPS.HRFV_ORGANIZATION_HIERARCHIES WHERE BUSINESS_GROUP_NAME = :p_bg ORDER BY ORGANIZATION_HIERARCHY_NAME, ORGANIZATION_HIERARCHY_LEVEL;

Resolve the parent of a specific organization using the searched column:

• SELECT CHILD_ORGANIZATION_ID, CHILD_ORGANIZATION_NAME, PARENT_ORGANIZATION_ID, PARENT_ORGANIZATION_NAME FROM APPS.HRFV_ORGANIZATION_HIERARCHIES WHERE CHILD_ORGANIZATION_ID = :p_org_id;

Filter to the primary hierarchy for the current security profile:

• SELECT CHILD_ORGANIZATION_ID, CHILD_ORGANIZATION_NAME, ORGANIZATION_HIERARCHY_LEVEL FROM APPS.HRFV_ORGANIZATION_HIERARCHIES WHERE PRIMARY_HIERARCHY_FLAG = 'Yes';

Note that security profile filtering is applied internally through GET_SEC_PROFILE_BG_ID, so results are automatically restricted to the business groups accessible to the querying user. The read-only nature of the view confirms it is intended for query access only, not for DML.