Results for “gms_report_templates_v”

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

Overview

GMS_REPORT_TEMPLATES_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined in the Grants Accounting (GMS) module. It presents grant report template definitions alongside the decoded meaning of each template's type classification. The view is valid in both EBS 12.1.1 and 12.2.2 and serves as the primary read interface for report templates used in sponsored program reporting, billing, and agency submissions.

Rather than exposing the raw TYPE code stored on the base table, the view joins to the lookup repository to resolve a human-readable MEANING via the REPORT_TYPE lookup type. This makes the view particularly valuable for reporting and integration scenarios where a functional label is required without a secondary lookup join in the consuming query.

Underlying Base Objects

The view is defined over two referenced objects:

• GMS_REPORT_TEMPLATES (referenced as a SYNONYM) — the base table holding one row per report template, including its name, type code, active date range, transaction number, agency-specific form flag, and standard WHO audit columns.
• GMS_LOOKUPS (a VIEW) — supplies the decoded lookup MEANING where LOOKUP_TYPE = 'REPORT_TYPE' and LOOKUP_CODE matches the template TYPE.

The join is an equality between LP.LOOKUP_CODE and RT.TYPE, restricted to the REPORT_TYPE lookup type. Because the join is an inner join, rows whose TYPE code has no matching active lookup entry are excluded from the view result set — an important consideration when reconciling counts against the base table. All columns except MEANING are sourced directly from GMS_REPORT_TEMPLATES.

Key Columns

• REPORT_TEMPLATE_ID — Primary identifier for the report template.
• REPORT_NAME — Descriptive name of the template as presented to users.
• TYPE — Lookup code that classifies the template (for example, report category or purpose).
• MEANING — Decoded, user-facing description of TYPE, resolved from GMS_LOOKUPS.
• TRANSACTION_NUMBER — Transaction reference associated with the template.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective date range governing when the template is usable.
• AGENCY_SPECIFIC_FORM — Flag indicating whether the template represents an agency-specific form.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ROWID — Standard audit and concurrency columns.

Common Use Cases and Queries

Typical uses include listing all report templates by functional type, filtering to currently active templates, and identifying agency-specific forms for submission planning. Supplying the search term report_type reflects a frequent need to enumerate or group templates by their decoded type.

List all templates with their type meaning:

SELECT report_template_id, report_name,
       type, meaning, agency_specific_form
FROM   apps.gms_report_templates_v;

Retrieve only currently active templates:

SELECT report_template_id, report_name,
       type, meaning
FROM   apps.gms_report_templates_v
WHERE  TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
                          AND NVL(end_date_active, TRUNC(SYSDATE));

Group by type to audit template distribution:

SELECT meaning, COUNT(*) template_count
FROM   apps.gms_report_templates_v
GROUP  BY meaning
ORDER  BY template_count DESC;

Because the view resolves the lookup internally, these queries avoid an additional join to GMS_LOOKUPS, simplifying reporting SQL and ensuring consistent labels.