Results for “organization_hierarchy_level”

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

Overview

PQH_PC_ORG_HIERARCHIES_V is an APPS-owned database view in Oracle EBS 12.1.1 and 12.2.2, defined within the Public Sector HR (PQH) product family. Its documented purpose is to expose the Position Control organization hierarchy for reporting. Position Control structures are a specialization of the standard Oracle HRMS organization hierarchy: they are the organization structures explicitly flagged as position-control structures, meaning that positions are budgeted and tracked against the organizations in the tree. The view flattens that hierarchy into a form directly consumable by reports and downstream integrations.

The view reflects the effective-dated nature of HRMS organization structures. It resolves the structure version that is current as of the effective date stored in the user's FND_SESSIONS record, so the hierarchy returned is the one valid for the session's effective date rather than a hard-coded "today." This makes it suitable for both transactional reporting and historical (as-of-date) reporting within the same query framework.

Underlying Base Objects

The documented base objects referenced by PQH_PC_ORG_HIERARCHIES_V are:

• PER_ORGANIZATION_STRUCTURES (synonym) — one row per organization structure; the POSITION_CONTROL_STRUCTURE_FLG column identifies which structures are Position Control structures.
• PER_ORG_STRUCTURE_VERSIONS (synonym) — the dated versions of each structure, supplying the DATE_FROM / DATE_TO validity window used to select the correct version.
• PER_ORG_STRUCTURE_ELEMENTS (synonym) — the parent/child organization links that make up the tree (ORGANIZATION_ID_PARENT, ORGANIZATION_ID_CHILD). This is the object the user searched for, and it is the primary driver of the view's hierarchy logic.
• FND_SESSIONS (synonym) — provides the session effective date used to filter structure versions.
• HR_GENERAL (package) — supplies DECODE_ORGANIZATION, used to translate organization IDs into display names.
• PQH_PSF_BUS (package) — supplies POSITION_CONTROL_ENABLED, used to mark whether a given organization has position control enabled.

The view text joins the three PER tables on structure, version, and element keys, restricts to structures where NVL(POSITION_CONTROL_STRUCTURE_FLG,'N') = 'Y', and applies the session-effective-date range. Its second half is a hierarchical query that starts from the root organizations — those parent organizations that never appear as a child in the position-control structure — and walks the tree level by level. The predicate POS.POSITION_CONTROL_STRUCTURE_FLG = 'Y' in the root-locating subquery is the effective equivalent of the NVL(...,'N') guard used earlier.

Key Columns

• RN — a row number; the seed/root rows are emitted with value 0, while rows from the hierarchical branch carry ROWNUM.
• LEVEL1 — the level of the organization within the hierarchy. Root rows are seeded at level 0; descendant rows are numbered by LEVEL.
• BUSINESS_GROUP_ID — the business group owning the structure.
• ORGANIZATION_ID — the organization identifier at each node; in the root query this is populated from ORGANIZATION_ID_PARENT.
• Decoded organization name — produced by HR_GENERAL.DECODE_ORGANIZATION, providing the reporting-friendly label for the business group and for each organization.
• Position-control enabled flag — the result of PQH_PSF_BUS.POSITION_CONTROL_ENABLED for the node's parent organization, indicating whether position control applies.

Common Use Cases and Queries

Typical scenarios include Position Control reporting by organization roll-up, validation of position-control tree integrity, and feeds into budget or staffing reports. A representative query is:

• SELECT rn, level1, organization_id, business_group_id FROM apps.pqh_pc_org_hierarchies_v ORDER BY level1, organization_id;
• Filtering by business group: ... WHERE business_group_id = :p_business_group_id ORDER BY level1;

Because the view derives its effective date from FND_SESSIONS, reliable results require an active, correctly initialised session; reports executed outside a normal EBS session context may not return the intended version. Performance depends on the hierarchical branch and on indexed access to PER_ORG_STRUCTURE_ELEMENTS; for large hierarchies, constrain by business group or organization where possible.