Results for “gms_dtrange_bal_v”

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

Overview

GMS_DTRANGE_BAL_V is a database view owned by the APPS schema in Oracle E-Business Suite, registered under the GMS – Grants Accounting product family. The view carries the status VALID in the EBS data dictionary and is documented in the E-Business Suite Technical Reference Manual (ETRM) for releases 12.1.1 and 12.2.2. The ETRM description identifies it with the annotation "- Retrofitted," indicating that the object was introduced or re-registered during an upgrade or retrofit effort rather than being part of the original Grants Accounting schema design.

Functionally, the view does not store data of its own. It presents a projected, de-duplicated subset of the GMS_BALANCES table restricted to budget-type balance rows. Its purpose is to expose the key dimensions of a budget balance record — project, award, budget version, and the effective date range — as a compact reference set. This makes it useful for reporting and integration scenarios where only the existence and effective period of a project-award budget version is required, without the debit, credit, and amount columns carried by the parent balances table.

Underlying Base Objects

The view is defined over a single referenced base object: GMS_BALANCES, accessed through the APPS.GMS_BALANCES synonym. GMS_BALANCES is the core balances fact table in Grants Accounting, storing period-aggregated amounts by project, award, budget version, and balance type. The view text filters that table with the predicate BALANCE_TYPE = 'BGT', so only budget-related balance rows qualify. Because the view selects DISTINCT on the projected column list, multiple underlying balance rows that share the same project, award, budget version, and date range collapse into a single row in the view. No joins, aggregations of numeric amounts, or security predicates appear in the documented view definition, so the view is essentially a lightweight dimension extract from the balances table.

Key Columns

• PROJECT_ID — Identifier of the project. The primary Projects dimension key and the value most commonly joined to PA_PROJECTS or related project views.
• AWARD_ID — Identifier of the award under which the budget balance was recorded; links to the GMS awards schema.
• BUDGET_VERSION_ID — Identifier of the budget version associated with the balance, distinguishing original, revised, and other budget versions for the same project-award combination.
• START_DATE — Beginning of the effective date range for the budget balance record.
• END_DATE — End of the effective date range for the budget balance record.

Together, these five columns form the distinct key of the view and describe when a given project-award budget version is in effect for balance purposes.

Common Use Cases and Queries

Typical use cases include validating which budget versions exist for a project or award, driving budget-version selection lists in custom reports, confirming effective date ranges before joining to GMS_BALANCES for amount detail, and reconciling interface extracts where only the budget header-level dimensions are needed.

A representative query listing budget versions and their effective ranges for a project is:

• SELECT project_id, award_id, budget_version_id, start_date, end_date FROM apps.gms_dtrange_bal_v WHERE project_id = :p_project_id ORDER BY budget_version_id;

To obtain period amounts for the same budget versions, join the view back to its source table on the shared keys:

• SELECT v.project_id, v.award_id, v.budget_version_id, b.balance_type, b.amount FROM apps.gms_dtrange_bal_v v, apps.gms_balances b WHERE b.project_id = v.project_id AND b.award_id = v.award_id AND b.budget_version_id = v.budget_version_id AND b.balance_type = 'BGT';

Because the view applies DISTINCT and restricts to BALANCE_TYPE 'BGT', queries should not expect one row per balance record; it is intended as a distinct dimension listing of budget versions and their effective ranges.