Search Results source_node_id

Overview

The PQH_CORPS_NATURE_HGRID_V view is a read-only database object owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PQH – Public Sector HR product family. In ETRM 12.2.2 it is documented as a VALID view with an HGrid ("hierarchical grid") purpose. The object name, together with the source text, identifies it as the data provider for the Corporate Nature hierarchy used by the Corps/HGrid user interface in Public Sector HR. The view flattens a multi-level organisational hierarchy spanning HGrid name lookups, organisation category (Nature) lookups, filere lookups, Corps definitions, and Corps grades into a single parent/child node structure suitable for rendering a tree control. Because the select list consistently exposes SOURCE_NODE_TYPE, DES_NODE_TYPE, SOURCE_NODE_ID, and DES_NODE_ID, downstream reporting or integration code can traverse the hierarchy without issuing multiple queries against each underlying base object.

Underlying Base Objects

The documented ETRM metadata lists DUAL (as a synonym) among referenced base objects, which reflects the tokenised extraction available in ETRM at 12.2.2. The actual view text, however, demonstrates that the view is a UNION of five node-producing branches, each sourced from real PQH and HR objects:

• HR_LOOKUPS — queried three times: for LOOKUP_TYPE = 'PQH_CORPS_HGRID_NAME' (the HGrid root), LOOKUP_TYPE = 'FR_PQH_ORG_CATEGORY' (Nature type), and LOOKUP_TYPE = 'PQH_CORPS_FILERES' (filere/personnel-file category).
• PQH_CORPS_DEFINITIONS — supplies CORPS_DEFINITION_ID, NAME, NATURE_TYPE_CD, and BUSINESS_GROUP_ID for the Corps-definition nodes.
• PQH_CORPS_FILERES_V — a companion view supplying FILERE_CD and FILERE_NAME, joined to PQH_CORPS_DEFINITIONS on CORPS_DEFINITION_ID.
• PQH_CORPS_GRADES (referenced as CGR) — supplies GRADE_ID and GRADE_NAME for the leaf (grade) level of the HGrid.
• HR_GENERAL — the PL/SQL package used in the WHERE clause via HR_GENERAL.GET_XBG_PROFILE and HR_GENERAL.GET_BUSINESS_GROUP_ID to enforce business-group security on the Corps-definition branch.

The union therefore links lookup-based reference data to transactional Corps/grade data, providing the multi-level HGrid tree in a single queryable structure.

Key Columns

• SOURCE_NODE_TYPE / DES_NODE_TYPE — Numeric level indicators (0 through 5) that identify which branch of the union a row originates from: 0 = HGrid, 1 = Nature root, 2 = Nature, 3 = Filere, 4 = Corps definition, 5 = Grade.
• DES_NODE_ID / DES_NODE_VALUE — The lookup code (or ID) and display meaning of the destination node in the hierarchy.
• SOURCE_NODE_ID — The parent node to which the current row is attached, enabling tree reconstruction.
• REGION_CODE — Populated as 'PQH_CORPS_DEF_UPDATE_PAGE' on the Corps-definition branch, allowing the UI to route the user to the appropriate maintenance page.
• NATURE_TYPE_CD / NATURE — The organisation category code and description from FR_PQH_ORG_CATEGORY.
• FILERE_CD / FILERE — The filere code and name carried from PQH_CORPS_FILERES_V.

Common Use Cases and Queries

The most frequent use is to populate the Corporate Nature hierarchical grid in the Corps Definition update page. The following query returns the full Nature–Filere–Corps tree for a business group:

SELECT source_node_type, des_node_type, source_node_id, des_node_id, des_node_value, region_code
FROM apps.pqh_corps_nature_hgrid_v
ORDER BY des_node_type, des_node_id;

A filtered query isolates the filere and Nature level, which is the most common search path when the user's first interaction is with filere_cd:

SELECT nature_type_cd, nature, filere_cd, filere
FROM apps.pqh_corps_nature_hgrid_v
WHERE des_node_type IN (2,3)
ORDER BY nature_type_cd, filere_cd;

For diagnostics, filtering on region_code = 'PQH_CORPS_DEF_UPDATE_PAGE' restricts output to Corps-definition nodes, which is useful when verifying that Business Group security is producing the expected set of records.