Results for “pqh_corps_definitions_n1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• NAME and BUSINESS_GROUP_ID — the corps name and the operating unit or business group that owns the definition.
• STATUS_CD, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, DATE_FROM, DATE_TO — lifecycle and date-tracked validity of the definition.
• CATEGORY_CD, CORPS_TYPE_CD, NATURE_TYPE_CD, TYPE_OF_PS — classification attributes identifying the category and type of corps.
• RETIREMENT_AGE, RECRUITMENT_END_DATE, PROBATION_PERIOD, PROBATION_UNITS — service rules governing retirement, recruitment cut-off, and probation.
• NORMAL_HOURS, NORMAL_HOURS_FREQUENCY, MINIMUM_HOURS, MINIMUM_HOURS_FREQUENCY — working-time expectations for the corps.
• SECONDMENT_THRESHOLD — threshold controlling secondment eligibility or duration.
• STARTING_GRADE_ID, STARTING_GRADE_STEP_ID, PRIMARY_PROF_FIELD_ID, BEN_PGM_ID — references to grade, grade step, professional field, and benefits program defaults.
• TASK_DESC and INFORMATION_TYPE/INFORMATION1–30 — descriptive text and descriptive-flexfield style information storage.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard EBS audit and concurrency columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–30 — developer extensibility attributes (DDF) for customer-specific configuration.

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.