Search Results pqh_corps_type

Overview

APPS.PQH_CORPS_DEFINITIONS_V is an Oracle E-Business Suite 12.1.1 / 12.2.2 reporting and integration view defined over the Corps Definitions entity in the Public Sector / HR (PQH) product family. A "corps" in this context represents a structured body of employees, typically associated with defense, police, teaching, or similar organized service groups whose terms of service, grading, hours, and retirement rules are centrally codified. The base table PQH_CORPS_DEFINITIONS stores the operational definition rows, while this view projects those rows together with decoded lookup descriptions and joined grade/spine information so that consumers are not required to perform their own lookup resolution or grade hierarchy joins. The view is owned by APPS and is intended for use from PL/SQL APIs, concurrent program extracts, Oracle Reports, OAF pages, and third-party integration layers that need a denormalized, human-readable representation of corps definitions.

Underlying Base Objects

The view text identifies the driving table as PQH_CORPS_DEFINITIONS (exposed to APPS as a synonym). It is joined to grade and spine tables through outer joins, using the starting_grade_step_id as the anchor. Specifically, PQH_CORPS_DEFINITIONS joins to PER_SPINAL_POINT_STEPS via starting_grade_step_id, to PER_GRADE_SPINES via grade_spine_id, to PER_PARENT_SPINES via parent_spine_id, to PER_SPINAL_POINTS via spinal_point_id, and to PER_GRADES_TL via grade_id. The grade translation table is filtered with grt.language = userenv('LANG') so that grade names are returned in the session language. Lookup translations are performed procedurally through the HR_GENERAL package function decode_lookup rather than through lookup view joins. All joins to the grade and spine objects are outer joins, meaning a corps definition remains visible even where no starting grade, spine, or spinal point has been configured.

Key Columns

• corps_definition_id, object_version_number, business_group_id — primary identifier, optimistic locking token, and the business group owning the definition.
• name — the corps definition name as entered by the user.
• status_cd / status_desc — the corps status code and its decoded description. The lookup type is PQH_CORPS_STATUS, which is the object referenced by the search term "pqh_corps_status". Filtering on status_cd is the typical way to restrict results to active or inactive definitions; status_desc is convenient for reporting output.
• category_cd / category_desc — decoded via PQH_CORPS_CATEGORY.
• corps_type_cd / type_desc — decoded via PQH_CORPS_TYPE.
• nature_type_cd / nature_type_desc — decoded via FR_PQH_ORG_CATEGORY.
• starting_grade, starting_step, scale, starting_grade_id, starting_grade_step_id — grade and spine placement information resolved through the outer-joined grade and spine objects.
• normal_hours / normal_hours_frequency / nor_frequency_desc — normal working hours and their decoded FREQUENCY value.
• minimum_hours / minimum_hours_frequency / min_frequency_desc — minimum hours and decoded frequency.
• retirement_age, secondment_threshold, tasks — retirement age, secondment threshold value, and the task description stored on the definition.
• effective_start_date, effective_end_date, recruitment_end_date — date-effectivity and recruitment controls for the definition.

Common Use Cases and Queries

Typical usage groups around status reporting, grade placement validation, and hours analysis. A status report is the most common: selecting the definition name, the decoded status, and the effective dates for a given business group.

SELECT corps_definition_id, name, status_desc, status_cd, effective_start_date, effective_end_date FROM apps.pqh_corps_definitions_v WHERE business_group_id = :p_bg_id AND status_cd = 'A' ORDER BY name;

Where the application code has only the decoded value available, filtering on status_desc is possible, but status_cd should be preferred for index efficiency and language independence. A grade placement query joins the view to PER_ALL_POSITIONS or to assignment records using starting_grade_id or starting_grade_step_id.

SELECT name, starting_grade, starting_step, scale FROM apps.pqh_corps_definitions_v WHERE starting_grade_id IS NOT NULL;

Hours analysis examines the minimum and normal hours columns together with their decoded frequency descriptions, while date-effectivity queries restrict on SYSDATE between effective_start_date and effective_end_date. Because PQH_CORPS_DEFINITIONS is date-effective, callers that require a point-in-time view must always apply an effective date predicate. The view itself imposes no date filter, so it returns every dated row held in the base table.