Search Results gms_status_accum_proj_base_v




Overview
Explore in AI Chat

Overview

APPS.GMS_STATUS_ACCUM_PROJ_BASE_V is a reporting view in the Oracle E-Business Suite Grants Accounting (GMS) module. Its name reflects its purpose: it aggregates (ACCUM) award and project balances by STATUS and presents them on a per-project (PROJ) basis. The view consolidates the three primary accounting classifications used in grants management — revenue, budget, and actual/encumbrance activity — into a single, horizontally structured result set suitable for ad-hoc inquiry, custom reports, and downstream integration into project status dashboards. It is a read-only, VALID object owned by the APPS schema and is available in both EBS 12.1.1 and 12.2.2. Because it joins summary balances, budget versions, and project master data, it is typically used where a Grants Administrator or an external reporting tool needs a normalized view of committed, obligated and realized amounts against an award and its associated projects.

Underlying Base Objects

The view is defined over several GMS and Oracle Projects base objects, all referenced through APPS synonyms:

  • GMS_BALANCES — the core source of period-to-date revenue, budget, actual and encumbrance figures, filtered by BALANCE_TYPE codes (REV, BGT, EXP, REQ, PO, AP, ENC).
  • GMS_BUDGET_VERSIONS — constrains the balances to the current or baseline budget version (CURRENT_FLAG IN ('Y','R')) and to accepted/released budget status (BUDGET_STATUS_CODE = 'B').
  • PA_PROJECTS_ALL — supplies the project number (SEGMENT1) and project name for each row.
  • GMS_AWARDS_ALL — provides the award context, including the award project identifier.
  • PA_EVENTS and GMS_EVENT_ATTRIBUTE — used in the second UNION ALL branch to capture manually distributed revenue events tied to an award.
  • GMS_BC_PACKETS — referenced in the third UNION ALL branch, which derives encumbrance-style amounts from entered debit and credit amounts by DOCUMENT_TYPE.

The view is constructed as a three-way UNION ALL: the first branch aggregates balances by award, project, segment and name; the second isolates manual revenue events; and the third normalizes packet debit/credit activity into expense, requisition, purchase order and AP buckets. The DECODE expressions pivot balance types into distinct columns, producing a wide, comparably aligned row structure.

Key Columns

  • AWARD_ID — the Grants award identifier, the primary grouping key.
  • PROJECT_ID and SEGMENT1 — the project identifier and user-facing project number.
  • NAME — the descriptive project name.
  • Revenue column — sum of REVENUE_PERIOD_TO_DATE for REV balance types, plus manual event revenue.
  • Budget column — sum of BUDGET_PERIOD_TO_DATE for BGT balance types.
  • Actual/Expenditure column — sum of ACTUAL_PERIOD_TO_DATE for EXP, or packet net debit-credit amounts for EXP document types.
  • Requisition, PO, AP and Encumbrance columns — each sums ENCUMB_PERIOD_TO_DATE (or packet net amounts) filtered by the corresponding balance or document type.

Common Use Cases and Queries

Typical uses include award-level financial status reports, budget-versus-actual comparisons, and reconciliation between GMS balances and Oracle Projects events.

SELECT AWARD_ID, PROJECT_ID, SEGMENT1, NAME
FROM   APPS.GMS_STATUS_ACCUM_PROJ_BASE_V
WHERE  AWARD_ID = :p_award_id;

To report budget versus actual by project:

SELECT SEGMENT1, NAME,
       SUM(budget_amt)  AS budget,
       SUM(actual_amt)  AS actual
FROM   APPS.GMS_STATUS_ACCUM_PROJ_BASE_V
GROUP  BY SEGMENT1, NAME;

Because the view consolidates three sources, care must be taken to filter on the required award or project in order to avoid full aggregation across all balances. It is best consumed read-only and not joined to large transactional tables without proper predicates.