Search Results pqh_corps_extra_info




Overview
Explore in AI Chat

Overview

PQH_CORPS_EXTRA_INFO is a Public Sector HR (PQH) transactional table in the Oracle E-Business Suite database, owned by the HR schema. It stores supplemental "corps extra information" records associated with Corps definitions, providing a flexible, denormalized repository for capturing user-defined detail data that is not represented by dedicated columns in adjacent Public Sector tables. In EBS 12.1.1 and 12.2.2 the table is documented as VALID and is referenced in the ETRM repository with a primary key named PQH_CORPS_EXTRA_INFO_PK.

The ETRM Data Vault classification for this object is standalone, mined heuristically from the foreign-key structure. Interpreted as a modeling suggestion, this indicates the table is neither a pure hub nor a link, and is best treated as an attribute-bearing satellite anchored to the Corps definition entity. Because only one foreign key is documented — CORPS_DEFINITION_ID referencing PQH_CORPS_DEFINITIONS — the table functions as a dependent detail store hanging off the Corps definition rather than as an independent master.

Key Information Stored

The physical schema documents 71 columns. The most significant are:

  • CORPS_EXTRA_INFO_ID — surrogate primary key, derived from the unique index PQH_CORPS_EXTRA_INFO_PK. This is the only documented unique index; no alternate business key is defined at the schema level.
  • CORPS_DEFINITION_ID — foreign key to PQH_CORPS_DEFINITIONS, establishing the parent Corps definition for each detail row.
  • INFORMATION_TYPE and INFORMATION_CATEGORY — classification columns that determine how the payload is interpreted.
  • INFORMATION1 through INFORMATION30 — thirty generic information slots holding the actual supplemental values. These are typically overloaded based on INFORMATION_TYPE.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE30 — the standard EBS descriptive-flexfield style attribute columns, enabling configurable, non-coded detail capture.
  • OBJECT_VERSION_NUMBER — optimistic locking and concurrency control for the row.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — the standard EBS audit Who columns.

The structure reflects the classic EBS pattern of combining a small set of typed columns with a large generic attribute block, avoiding schema changes as new Corps detail requirements emerge.

Common Use Cases and Queries

Typical scenarios involve reporting supplemental Corps attributes and reconciling detail rows back to their parent definitions.

  • Retrieve all extra information for a given Corps definition: 
    SELECT e.corps_extra_info_id, e.information_type,
       e.information1, e.attribute1
FROM   hr.pqh_corps_extra_info e
WHERE  e.corps_definition_id = :definition_id;
  • Join to the parent to produce a detail report: 
    SELECT d.corps_definition_id, e.information_category, e.attribute1
FROM   hr.pqh_corps_definitions d,
       hr.pqh_corps_extra_info e
WHERE  d.corps_definition_id = e.corps_definition_id;
  • Filter by information type to extract a specific payload category, or pivot the INFORMATION1..30 columns into a vertical report for downstream analytics.

Because the attribute columns are flexfield-style, reporting tools (BI Publisher, OTBI, or custom SQL) should always resolve the meaning of ATTRIBUTE_CATEGORY and INFORMATION_TYPE before interpreting the values.

Related Objects

  • PQH_CORPS_DEFINITIONS — parent entity; joined on CORPS_DEFINITION_ID.
  • PQH_CORPS_EXTRA_INFO_PK — the primary key index supporting unique row access.
  • HR schema public APIs / PL/SQL packages in the PQH module that populate and validate this table.
  • PQH_CORPS and associated Public Sector Corps tables that share the definition context.
  • Standard EBS audit/Who columns sourced from FND_USER via LAST_UPDATED_BY and CREATED_BY.

Administrators should treat this table as a dependent detail store and preserve referential integrity with PQH_CORPS_DEFINITIONS when purging or archiving Corps data.