Results for “pqh_corps_doc_type”

8 results




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

Overview

APPS.PQH_CORPS_DOCUMENTS_V is a reporting and integration view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the APPS schema. It presents a filtered, decode-enhanced projection of the PQH_CORPS_EXTRA_INFO table, exposing only those rows where INFORMATION_TYPE equals 'DOCUMENT'. The view belongs to the Public Sector/HR (PQH) product family and is used primarily to surface Corps of Engineers-style document metadata that is stored in the generic key-value extra information store.

Its principal advantage over querying the base table directly is that it presents a stable, column-named interface: consumers no longer need to know that "information3" holds the document type, "information4" holds the document number, and so on. It also joins in translation logic through HR_GENERAL.DECODE_LOOKUP so that a human-readable document type description is returned alongside the code. The user search term "document_type_cd" maps exactly to the INFORMATION3 column in the base table.

Underlying Base Objects

The view is defined over the following documented objects:

  • PQH_CORPS_EXTRA_INFO (SYNONYM) — the driving table, storing extra information records keyed by CORPS_DEFINITION_ID and CORPS_EXTRA_INFO_ID.
  • FND_DATE (PACKAGE) — invoked via FND_DATE.CANONICAL_TO_DATE to convert the stored canonical date string in INFORMATION6 into a true DATE value.
  • HR_GENERAL (PACKAGE) — invoked via HR_GENERAL.DECODE_LOOKUP('PQH_CORPS_DOC_TYPE', INFORMATION3) to produce the document type description.

A single WHERE predicate (INFORMATION_TYPE = 'DOCUMENT') restricts the result set, effectively partitioning the extra-info table into a document-specific logical view without physically copying data.

Key Columns

  • CORPS_DEFINITION_ID — identifier of the parent Corps definition; the primary grouping key linking documents to a definition.
  • CORPS_EXTRA_INFO_ID — unique identifier of the extra information row.
  • OBJECT_VERSION_NUMBER — optimistic locking/versioning column.
  • INFORMATION_TYPE — always 'DOCUMENT' through this view; the discriminator.
  • DOCUMENT_TYPE_CD — aliases INFORMATION3; the coded document type (search term target).
  • DOCUMENT_NUMBER — aliases INFORMATION4; the document identifier/number.
  • DOCUMENT_NAME — aliases INFORMATION5; the descriptive document title.
  • DOCUMENT_DATE — INFORMATION6 converted by FND_DATE.CANONICAL_TO_DATE.
  • DOCUMENT_TYPE_DESC — decoded description via lookup PQH_CORPS_DOC_TYPE.
  • DOCUMENT_URL — aliases INFORMATION7; the link/URL to the document.

Common Use Cases and Queries

Typical uses include listing attached documents for a Corps definition and retrieving document URLs for embedded display.

SELECT document_type_cd, document_type_desc, document_number,
       document_name, document_date, document_url
FROM   apps.pqh_corps_documents_v
WHERE  corps_definition_id = :p_definition_id
ORDER BY document_date;

Filtering by code:

SELECT corps_definition_id, document_number, document_name
FROM   apps.pqh_corps_documents_v
WHERE  document_type_cd = 'XYZ';

Because the view depends on HR_GENERAL and FND_DATE, ensure these packages are valid and that the 'PQH_CORPS_DOC_TYPE' lookup contains the referenced codes; otherwise DOCUMENT_TYPE_DESC may return NULL.