Results for “pqh_corps_documents_v”

18 results




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

Overview

The PQH_CORPS_DOCUMENTS_V view is a reporting and integration object in the Oracle E-Business Suite, owned by the APPS schema and validated in releases 12.1.1 and 12.2.2. It belongs to the PQH – Public Sector HR product family, whose name derives from the former "Position and Grade Hierarchy" / Corporate (CORPS) structures used to store supplemental information about organizations, positions, and related entities. The view is described in the ETRM metadata simply as the "Corp Document View," indicating its purpose: exposing the document-related records held in the generic CORPS extra-information store as a set of named, business-friendly columns.

Rather than reading the wide, sparsely populated PQH_CORPS_EXTRA_INFO table directly, consumers use this view to obtain typed columns such as DOCUMENT_TYPE_CD, DOCUMENT_NUMBER, DOCUMENT_NAME, DOCUMENT_DATE, and — most notably for integration and reporting — DOCUMENT_URL. The view therefore acts as a presentation layer that decodes lookup codes, converts stored date strings, and renames positional INFORMATIONn attributes into meaningful names. This makes it suitable for BI Publisher reports, Oracle BI / Discoverer queries, outbound interfaces, and any solution that needs to link a Corporate definition to an externally stored document via a URL.

Underlying Base Objects

The documented dependency list for APPS.PQH_CORPS_DOCUMENTS_V comprises three referenced objects:

  • PQH_CORPS_EXTRA_INFO (SYNONYM) — the single base object in the view's FROM clause, and the physical source of all document rows.
  • FND_DATE (PACKAGE) — used to convert the stored canonical date string in INFORMATION6 into a genuine DATE value through FND_DATE.CANONICAL_TO_DATE.
  • HR_GENERAL (PACKAGE) — used to translate the document type lookup through HR_GENERAL.DECODE_LOOKUP against the PQH_CORPS_DOC_TYPE lookup type.

The view is defined over PQH_CORPS_EXTRA_INFO with a filter restricting INFORMATION_TYPE to the value 'DOCUMENT'. Because the extra-information model stores mixed attribute sets in the same table, this predicate is essential: it isolates document records from other Corporate extra-information categories and guarantees that the positional columns (INFORMATION3 through INFORMATION7) carry document semantics. There is no join to the base Corporate definition table exposed through the view text; only CORPS_DEFINITION_ID is carried through, allowing callers to join to their own definition or organization tables as required.

Key Columns

  • CORPS_DEFINITION_ID — identifier of the Corporate definition (for example, an organization or position) to which the document record is attached; the primary join key to caller-supplied context tables.
  • CORPS_EXTRA_INFO_ID — surrogate key of the underlying extra-information row; unique per document record.
  • OBJECT_VERSION_NUMBER — optimistic locking version, populated from the base row.
  • INFORMATION_TYPE — always 'DOCUMENT' for rows returned by this view.
  • DOCUMENT_TYPE_CD — the raw lookup code, mapped from INFORMATION3.
  • DOCUMENT_NUMBER — the document's reference number, from INFORMATION4.
  • DOCUMENT_NAME — the descriptive document name/title, from INFORMATION5.
  • DOCUMENT_DATE — the document date, derived by converting the canonical date string in INFORMATION6 via FND_DATE.CANONICAL_TO_DATE.
  • DOCUMENT_TYPE_DESC — the decoded, user-facing description of the document type, resolved from the PQH_CORPS_DOC_TYPE lookup.
  • DOCUMENT_URL — the location (URL or path) of the stored document, mapped from INFORMATION7; this is the column most frequently used in integration scenarios.

Common Use Cases and Queries

A typical requirement is to list all documents for a given Corporate definition, including their type, number, and hyperlink:

  • Building a BI Publisher report or OAF region that renders document links for an organization or position.
  • Extracting document metadata for a content-management or imaging system interface.
  • Validating that every Corporate definition of interest has at least one document with a populated DOCUMENT_URL.

Representative SQL:

  • SELECT d.corps_definition_id, d.document_type_desc, d.document_number, d.document_name, d.document_date, d.document_url FROM apps.pqh_corps_documents_v d WHERE d.corps_definition_id = :p_definition_id ORDER BY d.document_date DESC;
  • SELECT d.corps_definition_id, COUNT(*) doc_count FROM apps.pqh_corps_documents_v d WHERE d.document_url IS NOT NULL GROUP BY d.corps_definition_id;
  • SELECT d.document_type_cd, d.document_type_desc, COUNT(*) FROM apps.pqh_corps_documents_v d GROUP BY d.document_type_cd, d.document_type_desc ORDER BY 3 DESC;

Because DOCUMENT_DATE is returned as a true DATE, date-range predicates and sorting can be applied directly without further conversion. Access should be granted through the APPS schema or a synonym, and responsibility-level security is enforced by joining CORPS_DEFINITION_ID to the appropriate organization or position security views.