Search Results pqh_corps_definitions




Overview
Explore in AI Chat

Overview

PQH_CORPS_DEFINITIONS is a Public Sector HR (PQH) configuration table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores Corps Definition records, which are the top-level setup entities used by public sector and defense-style deployments to model service corps, cadres, or organized workforce groupings. Each row defines a corps with its lifecycle attributes—activation status, effective dating, retirement age, recruitment windows, working-hour expectations, and grades related to entry into the corps. The table is therefore a reference and configuration object rather than a transactional fact table; it is read primarily by setup forms and downstream HR processes that classify employees, applicants, and assignments into a corps structure.

Approximately 95 columns are documented for the object, which reflects the typical EBS pattern of a narrow set of denormalized business attributes combined with reserved INFORMATION and ATTRIBUTE flex-style segments. The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the table participates in no outgoing foreign-key relationships and is not modeled as a hub, link, or satellite. As a modeling suggestion, it can be treated as a reference or lookup source that is itself referenced by satellite-style detail tables, rather than as a dependent entity within a larger key hierarchy.

Key Information Stored

The primary key is the surrogate column CORPS_DEFINITION_ID, enforced by the unique index PQH_CORPS_DEFINITIONS_PK, which is also the sole documented business-key candidate. Key business columns include:

Common Use Cases and Queries

Typical uses include validating active corps definitions per business group, driving eligibility and defaulting logic in public sector HR forms, and reporting on workforce configuration. A common pattern joins the definition to its extension table:

  • SELECT d.corps_definition_id, d.name, d.status_cd, d.effective_start_date FROM hr.pqh_corps_definitions d WHERE d.business_group_id = :bg AND d.status_cd = 'A';
  • SELECT d.name, e.corps_definition_id FROM hr.pqh_corps_definitions d, hr.pqh_corps_extra_info e WHERE d.corps_definition_id = e.corps_definition_id;
  • SELECT name, retirement_age, normal_hours, normal_hours_frequency FROM hr.pqh_corps_definitions WHERE category_cd = :cat;

Reporting frequently filters by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to produce point-in-time views, and by STATUS_CD to exclude retired definitions. The INFORMATION and ATTRIBUTE segments are read where customers have configured descriptive flexfields.

Related Objects

The most significant dependent object is PQH_CORPS_EXTRA_INFO, which references this table through CORPS_DEFINITION_ID and holds additional attributes per corps definition. Grades and grade steps are referenced by STARTING_GRADE_ID and STARTING_GRADE_STEP_ID, and benefits programs through BEN_PGM_ID, linking the definition to HR grade and benefit setup. Professional fields referenced by PRIMARY_PROF_FIELD_ID connect the corps to competency and professional structure. BUSINESS_GROUP_ID ties the definition to HR business group and organization configuration. The primary key CORPS_DEFINITION_ID is the join key used by any dependent public sector HR setup or assignment logic that classifies persons into a corps.