Search Results gms_res_v




Overview
Explore in AI Chat

Overview

GMS_RES_V is an APPS-owned database view in Oracle E-Business Suite, belonging to the GMS – Grants Accounting product. It is documented in ETRM as a retrofitted object, meaning its definition was carried forward from an earlier release into the 12.1.1 and 12.2.2 code lines without functional redesign. The view consolidates award, budget version, and resource-list data into a single reporting structure that presents budgeted, actual, and encumbrance amounts by project, award, task, and resource. It sits between the transaction-level detail held in the grants accounting base tables and the reporting layer used by Grants Accounting inquiry screens, budget-versus-actual reports, and downstream extract programs. Because it is a view rather than a table, GMS_RES_V provides no persistent storage; it projects and aggregates data at query time, which keeps it consistent with the current state of awards and budgets but also means its performance depends heavily on the underlying structures.

Underlying Base Objects

The view is defined over a join of GMS_AWARD_BAL_V, GMS_BUDGET_VERSIONS (synonym), PA_BUDGET_ENTRY_METHODS (synonym), PA_RESOURCES (synonym), and PA_RESOURCE_LIST_MEMBERS (synonym). Supporting logic is supplied by the GMS_BUDGET_UTILS package, invoked in the SELECT list through GET_TASK_NUMBER to resolve task numbers, and by PA_CROSS_BUSINESS_GRP and FND_PROFILE, which are referenced by the surrounding Grants Accounting architecture. PA_RESOURCE_LISTS (view) participates through a correlated EXISTS subquery that restricts output to members belonging to resource lists whose group resource type is zero. The join condition ties each GMS_AWARD_BAL_V row to its budget version via BUDGET_VERSION_ID, then to the entry method defined on that version, and finally to the resource list member and resource name. GMS_AWARD_BAL_V is the principal driver, supplying the budget, actual, and encumbrance figures that are summed by the view.

Key Columns

The view exposes fourteen columns. PROJECT_ID, AWARD_ID, TASK_ID, and TOP_TASK_ID identify the award structure being reported. TASK carries the task number returned by GMS_BUDGET_UTILS.GET_TASK_NUMBER. RESOURCE_LIST_MEMBER_ID and RESOURCE_NAME identify the resource, with the name resolved from PA_RESOURCES. BUDGET_VERSION_ID links each row to the specific budget version. PARENT_MEMBER_ID reflects the decoded parent member logic, collapsing the hierarchy where required. START_DATE and END_DATE bound the reporting period. The three measures—BUDGET, ACTUALS, and ENCUMBRANCES—are each aggregated with SUM and grouped by the remaining columns. A notable behavior is the use of DECODE on the budget entry method code: when the method is project-level ('P'), TASK_ID and TOP_TASK_ID are forced to zero and TASK is set to NULL, ensuring project-level budgets are not misattributed to a task.

Common Use Cases and Queries

The view is typically queried for budget-versus-actual analysis across awards and tasks, and for feeding summary extracts that compare planned funding to committed and expended amounts. A basic query would be:

  • SELECT project_id, award_id, task, resource_name, budget, actuals, encumbrances FROM apps.gms_res_v WHERE award_id = :award_id AND budget_version_id = :version_id ORDER BY task, resource_name;
  • SELECT project_id, SUM(budget) budget, SUM(actuals) actuals, SUM(encumbrances) encumbrances FROM apps.gms_res_v WHERE budget_version_id = :version_id GROUP BY project_id;
  • SELECT resource_name, SUM(actuals) FROM apps.gms_res_v WHERE project_id = :project_id AND start_date >= :from_date GROUP BY resource_name;

Because the view aggregates internally, filters on PROJECT_ID, AWARD_ID, BUDGET_VERSION_ID, or START_DATE are the most effective predicates. Queries should avoid unfiltered full scans, since the underlying joins and the EXISTS subquery on PA_RESOURCE_LISTS can be costly on large award portfolios.