Search Results training_duration




Overview
Explore in AI Chat

Overview

PQH_CORPS_TRAININGS_V is an APPS-owned database view within the Oracle E-Business Suite Public Sector HR (PQH) product family. It exposes training-related qualification records captured against CORPS definitions, presenting them in a denormalized, human-readable form suitable for reporting, integration, and functional inquiry. The view is documented as VALID in ETRM for releases 12.1.1 and 12.2.2 and is registered in the APPS schema.

Its central purpose is to translate the generic columnar structure of PQH_CORPS_EXTRA_INFO — where CORPS attributes are stored positionally as INFORMATION1 through INFORMATIONn — into meaningful business columns such as QUALIFICATION, INSTITUTION_NAME, TRAINING_DURATION, and MANDATORY_FLAG. Because the view joins to qualification and organization reference data, consumers avoid re-implementing lookup and decode logic. This makes it a preferred source for reports, extracts, and interfaces that need CORPS training content without direct knowledge of the underlying flexfield-style storage.

Underlying Base Objects

The view is defined over three directly referenced sources and depends on several additional objects for decoding and security. Documented base and dependent objects are:

  • PQH_CORPS_EXTRA_INFO (synonym) — the primary driving table. Columns INFORMATION_TYPE, INFORMATION3 through INFORMATION8 supply qualification, institution, duration, joining way, and mandatory flag values.
  • PER_QUALIFICATION_TYPES_TL (synonym) — aliased QUA; joined on QUALIFICATION_TYPE_ID = CEI.INFORMATION3 to resolve QUA.NAME as QUALIFICATION.
  • HR_ORGANIZATION_UNITS (view) — aliased ORG; joined on ORGANIZATION_ID = CEI.INFORMATION4 to resolve ORG.NAME as INSTITUTION_NAME.
  • HR_GENERAL (package) — provides DECODE_LOOKUP, used to translate INFORMATION6 (duration UOM) via the FREQUENCY lookup and INFORMATION7 (joining way) via PQH_CORPS_WAYS.
  • HR_SECURITY (package) — documented dependency supplying row-level security for organization-based access.

The defining predicate CEI.INFORMATION_TYPE = 'TRAINING' restricts output to training records, excluding other CORPS extra information categories stored in the same table.

Key Columns

  • CORPS_DEFINITION_ID — identifies the CORPS definition to which the training record belongs.
  • CORPS_EXTRA_INFO_ID — surrogate key of the underlying PQH_CORPS_EXTRA_INFO row; useful for drill-through.
  • OBJECT_VERSION_NUMBER — optimistic locking column carried from the base row.
  • INFORMATION_TYPE — always 'TRAINING' in this view; carried through as a discriminator.
  • QUALIFICATION / QUALIFICATION_ID — the qualification name resolved from PER_QUALIFICATION_TYPES_TL and its underlying ID (INFORMATION3). Users searching on qualification_id typically filter here.
  • INSTITUTION_NAME / INSTITUTION_ID — organization name and ID (INFORMATION4) of the awarding or delivering institution.
  • TRAINING_DURATION / TRAINING_DURATION_UOM / UOM_DESC — numeric duration, its lookup code, and the decoded description from the FREQUENCY lookup.
  • JOINING_WAY_CD / JOINING_WAY — raw code and decoded description from PQH_CORPS_WAYS.
  • MANDATORY_FLAG — indicates whether the training is required.

Common Use Cases and Queries

Typical uses include CORPS training inventories, qualification compliance reporting, integration extracts to external HR or LMS systems, and institution-level training analytics.

Retrieve all training for a CORPS definition:

SELECT corps_definition_id, qualification, institution_name,
       training_duration, uom_desc, mandatory_flag
FROM   apps.pqh_corps_trainings_v
WHERE  corps_definition_id = :p_corps_definition_id;

Filter by qualification, matching the common qualification_id search pattern:

SELECT corps_definition_id, qualification, institution_name
FROM   apps.pqh_corps_trainings_v
WHERE  qualification_id = :p_qualification_id;

List mandatory training by institution:

SELECT institution_name, qualification, joining_way
FROM   apps.pqh_corps_trainings_v
WHERE  mandatory_flag = 'Y'
ORDER BY institution_name, qualification;

Because organization security is enforced through HR_SECURITY on the underlying organization join, results are automatically restricted according to the runtime user's security profile, making the view safe for shared reporting responsibilities.