Search Results pqh_corps_rules_v




Overview
Explore in AI Chat

Overview

PQH_CORPS_RULES_V is an APPS-owned database view within the Public Sector HR (PQH) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. The view exposes a decoded, presentation-ready projection of the Corps rules configuration stored in the underlying extra information table. Its status is VALID and it is documented in ETRM as the "Corp Rules View."

Functionally, the view provides a business-friendly abstraction over raw code-based configuration. Rather than requiring consumers to interpret lookup codes directly, PQH_CORPS_RULES_V translates stored codes into descriptive values using the HR_GENERAL.DECODE_LOOKUP API. The view filters the base data so that only rows of INFORMATION_TYPE = 'RULES' are returned, isolating the rules-configured attributes from the other extra-information categories that share the same table. This makes the view suitable for reporting, OBIEE/BI Publisher extracts, custom concurrent programs, and integration layers that need to surface corps rule definitions without embedding decode logic in each consumer.

Underlying Base Objects

The view is defined over a single synonym, PQH_CORPS_EXTRA_INFO, which resolves to the extra-information entity in the PQH schema, in combination with the HR_GENERAL package. The ETRM metadata lists the referenced base objects as HR_GENERAL (PACKAGE) and PQH_CORPS_EXTRA_INFO (SYNONYM).

The HR_GENERAL package participates through its DECODE_LOOKUP function, which is invoked three times in the view text to resolve lookup meanings for the joining way, situation, and frequency/UOM columns. The PQH_CORPS_EXTRA_INFO table supplies the core attribute columns (CORPS_DEFINITION_ID, CORPS_EXTRA_INFO_ID, OBJECT_VERSION_NUMBER, INFORMATION_TYPE, and INFORMATION3 through INFORMATION7). Because the view joins to the lookup decode logic rather than storing redundant descriptive columns, the descriptive values always reflect the current lookup configuration at query time.

Key Columns

The view exposes twelve columns. The primary identifiers and versioning columns are:

  • CORPS_DEFINITION_ID — foreign key linking the rule row to its parent Corps definition.
  • CORPS_EXTRA_INFO_ID — the unique identifier of the extra-information record.
  • OBJECT_VERSION_NUMBER — optimistic locking/versioning indicator used by the underlying entity.
  • INFORMATION_TYPE — always 'RULES' for rows visible through this view.

The decoded rule attributes, paired with their source codes, include:

  • JOINING_WAY and JOINING_WAY_CD — the descriptive and coded values of the joining method, decoded from the PQH_CORPS_WAYS lookup applied to INFORMATION3. This is the column that corresponds to the user's search term "pqh_corps_ways."
  • SITUATION and SITUATION_CD — the descriptive and coded values of the rule situation, decoded from the PQH_CORPS_WAYS_CRITERIA lookup applied to INFORMATION4.
  • VALUE — the rule value held in INFORMATION5.
  • UOM and UOM_DESC — the unit-of-measure code (INFORMATION6) and its descriptive meaning decoded from the FREQUENCY lookup.
  • MANDATORY_FLAG — indicates whether the rule is mandatory, held in INFORMATION7.

Common Use Cases and Queries

The view is typically queried to report or extract the rules governing a Corps definition. A basic query retrieves all decoded rules for a given definition:

SELECT corps_definition_id, joining_way, situation, value, uom_desc, mandatory_flag
FROM   apps.pqh_corps_rules_v
WHERE  corps_definition_id = :p_definition_id;

A common validation scenario filters on mandatory rules to confirm configuration completeness:

SELECT joining_way, situation, value, uom_desc
FROM   apps.pqh_corps_rules_v
WHERE  mandatory_flag = 'Y'
ORDER  BY joining_way, situation;

Because the descriptive columns are resolved through HR_GENERAL.DECODE_LOOKUP, results reflect current lookup values; integration consumers that require stability should key off the coded columns (JOINING_WAY_CD, SITUATION_CD, UOM) rather than the decoded text. Typical uses include BI Publisher rule reports, custom concurrent program extracts into staging tables, and diagnostic queries that verify that a Corps definition carries the expected set of joining ways and criteria.