Search Results pqh_corps_definitions_pk

Overview

The HR.PQH_CORPS_DEFINITIONS table is a core configuration and reference table within the Oracle E-Business Suite HR schema, holding the definitions of "corps" — organizational groupings used primarily in public-sector and defense-related HR deployments to represent bodies of personnel such as military corps, service corps, or similar structured groups. In Oracle EBS 12.1.1 and 12.2.2, the table anchors the Corps program within Human Resources, providing the header-level attributes that downstream assignments, position placements, and reporting depend upon.

The object is documented as VALID, owned by the HR schema, with FND design data registered under the PQH product (Public Sector HR). It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes live in APPS_TS_TX_IDX. With a documented 95 columns, the table follows the standard Oracle EBS extensibility pattern of seeded product columns followed by a large block of descriptive flexfield (DFF) and attribute columns.

From a Data Vault modeling perspective, the heuristic classification supplied with this metadata is standalone, which suggests the table behaves as a self-contained hub entity rather than a link or satellite. In Data Vault terms, CORPS_DEFINITION_ID serves as the natural hub key, while the descriptive attributes around it would typically be modeled as one or more satellites.

Key Information Stored

Each row represents a single corps definition, scoped by business group. The most significant columns are:

• CORPS_DEFINITION_ID — NUMBER(15), the surrogate primary key and the column used by the unique index PQH_CORPS_DEFINITIONS_PK. This is the identifier referenced by all dependent tables.
• BUSINESS_GROUP_ID — NUMBER(15), the business group (operating unit) that owns the definition, central to multi-org filtering.
• NAME — VARCHAR2(240), the descriptive corps name and the business-key candidate backed by the non-unique index PQH_CORPS_DEFINITIONS_N1.
• STATUS_CD — VARCHAR2(30), the lifecycle status of the corps definition.
• CORPS_TYPE_CD and CATEGORY_CD — VARCHAR2(30) codes classifying the corps and its category.
• STARTING_GRADE_STEP_ID and STARTING_GRADE_ID — NUMBER(15) references to the grade and grade step applied on entry into the corps.
• RETIREMENT_AGE — NUMBER(10), the retirement age associated with the corps.
• NORMAL_HOURS, NORMAL_HOURS_FREQUENCY, MINIMUM_HOURS, and MINIMUM_HOURS_FREQUENCY — the working-hour rules that govern the corps.
• SECONDMENT_THRESHOLD — NUMBER, the threshold governing secondment eligibility.
• BEN_PGM_ID — NUMBER, linking the corps to a benefits program, indexed non-uniquely via PQH_CORPS_DEFINITIONS_N2.
• RECRUITMENT_END_DATE and PROBATION_PERIOD with PROBATION_UNITS — recruitment and probation rules.
• ATTRIBUTE1–ATTRIBUTE30 and INFORMATION1–INFORMATION30 — the standard EBS DFF/extra-information placeholders used for client-specific configuration.
• OBJECT_VERSION_NUMBER and the standard WHO audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Several legacy columns, including EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, NATURE_TYPE_CD, INFORMATION_TYPE, and the INFORMATIONn block, are documented as obsoleted and should not be used in new development.

Common Use Cases and Queries

Typical uses include validating corps setup during implementation, reporting on corps configuration for benefits and grade-step mapping, and joining corps definitions to employee or assignment data for public-sector reporting.

A standard lookup by primary key mirrors the user's search on the PK index:

• SELECT * FROM hr.pqh_corps_definitions WHERE corps_definition_id = :id;
• SELECT corps_definition_id, name, status_cd FROM hr.pqh_corps_definitions WHERE business_group_id = :bg_id AND name = :name; — resolved through index PQH_CORPS_DEFINITIONS_N1.
• SELECT c.corps_definition_id, c.name, b.program_name FROM hr.pqh_corps_definitions c, ben_benefit_programs b WHERE c.ben_pgm_id = b.program_id; — resolved through N2.

Reporting scenarios frequently filter on CORPS_TYPE_CD or CATEGORY_CD, and developers building DFF-driven extensions should query ATTRIBUTE_CATEGORY alongside the ATTRIBUTE1–ATTRIBUTE30 columns to interpret context-sensitive values.

Related Objects

The documented foreign-key relationship shows one child table: PQH_CORPS_EXTRA_INFO.CORPS_DEFINITION_ID references HR.PQH_CORPS_DEFINITIONS. Additional associations derive from the columns and indexes present:

• HR.PQH_CORPS_EXTRA_INFO — child table holding supplementary corps attributes; join on CORPS_DEFINITION_ID.
• BEN_BENEFIT_PROGRAMS (or its runtime view) — referenced by BEN_PGM_ID and indexed via PQH_CORPS_DEFINITIONS_N2.
• PER_GRADES / PER_GRADE_STEPS — referenced by STARTING_GRADE_ID and STARTING_GRADE_STEP_ID.
• HR_ALL_ORGANIZATION_UNITS — the source of the BUSINESS_GROUP_ID value used to scope rows to an operating unit.
• FND_DESCR_FLEX_COLUMN_USAGES — the DFF metadata that defines the ATTRIBUTE_CATEGORY/ATTRIBUTE1–30 extensibility model on this table.

Because the table is classified as standalone, no parent hub is implied; all referential integrity flows outward from CORPS_DEFINITION_ID.