Search Results per_cal

Overview

APPS.PER_GEN_HIER_NODE_TYPES_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 Oracle Human Resources (PER) schema that exposes the configuration of generic hierarchy node types. It is a filtered, decode-enriched projection of the PER_GEN_HIER_NODE_TYPES entity, returning only rows whose HIERARCHY_TYPE matches the pattern 'PER_CAL%'. This filter scopes the view to hierarchy types associated with the PER_CAL (Person Calendar) hierarchy family, which supports the assignment and placement of employees and other person-level nodes.

The view serves reporting, integration, and diagnostic purposes. Rather than exposing raw lookup codes, it joins to HR_LOOKUPS twice to resolve both the hierarchy type meaning and the child node type meaning into human-readable text, along with a description of the child node type. This makes it well suited for BI Publisher reports, extracts, and interface development where descriptive hierarchy metadata is required without embedding lookup decoding logic in downstream SQL.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects:

• PER_GEN_HIER_NODE_TYPES (SYNONYM) — the primary source of hierarchy node type definitions, aliased PGT in the view text. Synonym resolution points to the underlying PER base table of the same name.
• HR_LOOKUPS (VIEW) — joined twice (HRL1 and HRL2) to resolve lookup codes into meanings and descriptions.
• HR_API (PACKAGE) — documented as a referenced object, indicating the view participates in the HR API layer that governs hierarchy configuration and validation.

The joins are constrained by two lookup types: 'HIERARCHY_TYPE' for HRL2 and 'HIERARCHY_NODE_TYPE' for HRL1. Both joins relate on LOOKUP_CODE equality, producing a fully decoded result set. Because HR_LOOKUPS is itself a view over HR lookup tables, changes to lookup meanings are reflected immediately at query time.

Key Columns

• HIER_NODE_TYPE_ID — primary identifier for the hierarchy node type configuration row.
• HIERARCHY_TYPE — the hierarchy classification code; restricted to values beginning with 'PER_CAL'.
• HRL2.MEANING — the decoded, user-facing meaning of the HIERARCHY_TYPE lookup.
• PARENT_NODE_TYPE / CHILD_NODE_TYPE — define the permitted parent-child relationship between node type lookup codes within the hierarchy.
• HRL1.MEANING — decoded meaning of the child node type.
• CHILD_VALUE_SET — identifies the value set used to validate child node values.
• HRL1.DESCRIPTION — descriptive text for the child node type lookup.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER provide standard EBS who-column auditing and optimistic locking support.

Common Use Cases and Queries

Typical uses include validating hierarchy configuration before loading person calendar hierarchies, generating metadata extracts for integration with external scheduling or HR systems, and supporting troubleshooting of hierarchy node placement errors.

A basic query listing all PER_CAL hierarchy node type relationships:

SELECT hier_node_type_id,
       hierarchy_type,
       child_node_type,
       child_value_set
FROM   apps.per_gen_hier_node_types_v
ORDER  BY hierarchy_type, child_node_type;

Resolving parent-child pairs with decoded meanings:

SELECT v.hierarchy_type Meaning_Type,
       v.parent_node_type,
       v.child_node_type,
       v.child_value_set
FROM   apps.per_gen_hier_node_types_v v
WHERE  v.parent_node_type IS NOT NULL;

Because the view already applies the 'PER_CAL%' filter and performs both lookup joins, consumers should not re-implement lookup decoding. Queries should filter or order using the exposed decoded columns where possible to align with the intended use of the view.