Search Results starting_grade

Overview

PQH_CORPS_DEFINITIONS_V is an APPS-owned database view within the Public Sector HR (PQH) product family of Oracle E-Business Suite. It presents a denormalized, reporting-friendly projection of corps definitions — the top-level configuration records that describe a public sector corps, including its status, category, starting grade and step, working-hour rules, and recruitment parameters. The base transactional entity is stored in PQH_CORPS_DEFINITIONS; the view enriches that record with decoded lookup meanings and joined grade-spine attributes so that consumers do not need to resolve lookup codes themselves.

The view is documented as VALID in both ETRM 12.1.1 and 12.2.2. Its primary role is to support inquiry screens, concurrent reports, and external integrations that need to display or extract corps definitions in human-readable form. Because the view resolves descriptive lookups (for example, PQH_CORPS_STATUS, PQH_CORPS_CATEGORY, PQH_CORPS_TYPE) and joins grade structures, it is the natural source for any extract where a user expects the status and category descriptions rather than their internal codes. This is particularly relevant when users search for the column status_desc, which is precisely the decoded form of STATUS_CD.

Underlying Base Objects

The view is defined over eight referenced objects. The driving table is PQH_CORPS_DEFINITIONS (referenced as a synonym), aliased CPD, which supplies all corps-level attributes including primary key CORPS_DEFINITION_ID, BUSINESS_GROUP_ID, OBJECT_VERSION_NUMBER, and the effective dating columns.

The lookup decoding is performed by the HR_GENERAL package, specifically the DECODE_LOOKUP function, which is invoked against lookup types PQH_CORPS_STATUS, PQH_CORPS_CATEGORY, FR_PQH_ORG_CATEGORY, PQH_CORPS_TYPE, and FREQUENCY. Each call returns the meaning for the stored code and is aliased into a *_DESC column.

Grade and step information is resolved through the PER_GRADES_TL synonym, the PER_GRADE_SPINES view, the PER_PARENT_SPINES synonym, the PER_SPINAL_POINTS synonym, and the PER_SPINAL_POINT_STEPS view. The join path, expressed with outer joins throughout the grade chain, links the corps definition's STARTING_GRADE_STEP_ID to a spinal point step, then to its grade spine, parent spine, spinal point, and grade. This construct ensures the corps definition row is never lost when grade configuration is absent.

Key Columns

• CORPS_DEFINITION_ID — Primary key of the corps definition.
• OBJECT_VERSION_NUMBER — Optimistic locking version, exposed for integration concurrency control.
• BUSINESS_GROUP_ID — Owning business group, used for multi-organization filtering.
• NAME — User-defined corps name.
• STATUS_CD / STATUS_DESC — Code and decoded description of the corps status lookup (PQH_CORPS_STATUS). STATUS_DESC is the column most commonly searched for by report authors.
• CATEGORY_CD / CATEGORY_DESC — Corps category code and decoded meaning.
• TYPE_CD / TYPE_DESC — Corps type code and decoded meaning.
• NATURE_TYPE_CD / NATURE_TYPE_DESC — Organization-nature classification of the corps.
• STARTING_GRADE, STARTING_STEP, SCALE, STARTING_GRADE_ID, STARTING_GRADE_STEP_ID — Grade-spine attributes describing the entry point for the corps.
• RETIREMENT_AGE — Configured retirement age for the corps.
• NORMAL_HOURS / MINIMUM_HOURS and their FREQUENCY columns and *_DESC companions — Standard and minimum working hours, with decoded frequency meanings.
• SECONDMENT_THRESHOLD, TASKS — Secondment threshold and task description text.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, RECRUITMENT_END_DATE — Date-effective and recruitment window attributes.

Common Use Cases and Queries

The view is typically queried for validation reports, HR setup listings, and inbound/outbound integration extracts. A common requirement is listing all active corps with their decoded status and category for a business group:

SELECT corps_definition_id, name, status_desc, category_desc, type_desc
FROM apps.pqh_corps_definitions_v
WHERE business_group_id = :p_business_group_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND NVL(effective_end_date, TRUNC(SYSDATE));

A second frequent pattern is joining the view to assignment or position data to enforce corps rules such as minimum hours, or to drive secondment eligibility checks using SECONDMENT_THRESHOLD. Because the view already outer-joins the grade chain, it can also be used to report the starting grade and step for each corps without additional joins. When only the descriptive status is needed — the scenario implied by the "status_desc" search — a minimal projection of NAME and STATUS_DESC filtered by STATUS_CD offers the most efficient access path, since the grade-spine joins are unnecessary and the lookup decoding is applied only to the selected rows.