Search Results pqh_templates_vl




Overview
Explore in AI Chat

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

Overview

PQH_TEMPLATES_VL is a translated (VL) view owned by the APPS schema in Oracle E-Business Suite, belonging to the PQH — Public Sector HR product family. Its documented description is "Templates View," reflecting its role as the reporting and integration surface for position and HR template definitions used throughout Public Sector HR and related Oracle iRecruitment, Oracle Learning Management, and Oracle Human Resources flows. The view is documented as VALID in ETRM for both 12.1.1 and 12.2.2. As a _VL view, it joins a base (_B) table to its corresponding translation (_TL) table, but unlike standard _VL views it does not expose a canonical _B table: the base table is PQH_TEMPLATES, and translation is supplied by PQH_TEMPLATES_TL. The LANGUAGE = USERENV('LANG') predicate returns the translation matching the session language, so the same row may render different TEMPLATE_NAME values depending on the runtime locale. The view is the standard, supported way to query template metadata without writing the join manually.

Underlying Base Objects

The documented referenced base objects are two synonyms: PQH_TEMPLATES (the transactional/base table) and PQH_TEMPLATES_TL (the translation table). The view joins them on TEMPLATE_ID. All non-name columns are selected from PQH_TEMPLATES (aliased TEM), while TEMPLATE_NAME is selected from PQH_TEMPLATES_TL (aliased TTL) filtered by TTL.LANGUAGE = USERENV('LANG'). Because the view filters on the language environment variable, a row is returned only when a translation exists for the session language. ROW_ID is exposed via TEM.ROWID, giving callers a stable, updatable row handle suitable for Forms-based and programmatic DML. Note that the join is an inner join: if the translation row is missing for the requested language, the base template will not appear.

Key Columns

  • TEMPLATE_ID — Primary key of the template; the join key between PQH_TEMPLATES and PQH_TEMPLATES_TL.
  • SHORT_NAME — Internal short identifier for the template.
  • TEMPLATE_NAME — Language-sensitive display name, sourced from PQH_TEMPLATES_TL.
  • UNDER_REVIEW_FLAG — The column targeted by the search term. Indicates whether the template is currently under review and therefore should not be treated as fully approved or active. Consumers performing reporting or integration should filter on this flag to exclude unapproved templates.
  • ENABLE_FLAG — Whether the template is enabled for use.
  • CREATE_FLAG — Whether the template may be used to create new records.
  • ATTRIBUTE_ONLY_FLAG — Distinguishes attribute-only templates from full templates.
  • TRANSACTION_CATEGORY_ID — Foreign key to the transaction category classification.
  • FREEZE_STATUS_CD — Freeze/status code controlling whether the template may be modified or superseded.
  • TEMPLATE_TYPE_CD — Code classifying the template type.
  • LEGISLATION_CODE — Legislation the template is valid for; important for multi-country deployments.
  • Standard WHO columns — OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and ROW_ID.

Common Use Cases and Queries

Typical scenarios include extracting approved templates, filtering out items under review, resolving localized names, and joining template definitions to transaction categories.

-- List active templates not under review for the current session language
SELECT template_id, short_name, template_name,
       template_type_cd, legislation_code, freeze_status_cd
FROM   apps.pqh_templates_vl
WHERE  enable_flag    = 'Y'
AND    under_review_flag = 'N'
ORDER BY template_name;

-- Count templates by review state and legislation
SELECT legislation_code, under_review_flag, COUNT(*) template_count
FROM   apps.pqh_templates_vl
GROUP BY legislation_code, under_review_flag
ORDER BY legislation_code, under_review_flag;

Because UNDER_REVIEW_FLAG and ENABLE_FLAG are frequently combined in eligibility checks, they are the most common predicates against this view. The view also supports joins to transaction categories via TRANSACTION_CATEGORY_ID and to other HR objects via TEMPLATE_ID. Always qualify the view with the APPS schema and rely on USERENV('LANG') being set appropriately in concurrent programs and OAF pages so that TEMPLATE_NAME resolves correctly.