Search Results pa_date




Overview
Explore in AI Chat

Overview

GMS_STATUS_ACTUALS_BASE1_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Grants Accounting (GMS) product family. Its purpose is to consolidate award-level actuals — revenue, billed, raw cost, and burdened cost amounts — into a single denormalized structure keyed by award, project, task, and expenditure item. The view is registered as VALID in both the 12.1.1 and 12.2.2 releases and is referenced by grant status inquiry and reporting logic that reconciles award funding against project cost activity.

The view serves as an intermediate aggregation layer rather than a transactional table. It unions two distinct sources: distribution-level cost rows from GMS award distributions joined to PA cost distribution lines, and burden component events that carry revenue or invoice event types. The union is then grouped so that each award/project/task/expenditure item combination yields a single row containing summarized REVENUE_AMOUNT, BILLED_AMOUNT, RAW_COST, and BURDENED_COST values. Because the view exposes BILLED_AMOUNT directly, it is frequently targeted by queries that attempt to report invoiced or billed amounts against sponsored awards.

Underlying Base Objects

The documented base objects referenced by this view are:

Joins are driven by EXPENDITURE_ITEM_ID combined with line number matching (CDL.LINE_NUM = ADL.CDL_LINE_NUM), and for the burden component branch additional matching on ADL_LINE_NUM. Only rows with DOCUMENT_TYPE = 'EXP' and ADL_STATUS = 'A' are included. The view therefore depends directly on Projects (PA) cost distribution data and on Grants Accounting award distribution data; without consistent expenditure item identifiers across both schemas, the join yields no rows.

Key Columns

  • AWARD_ID, PROJECT_ID, TASK_ID — the award and project/task hierarchy keys under which actuals are accumulated.
  • EXPENDITURE_ITEM_ID — the finest granularity retained in the grouping; each row represents one expenditure item.
  • REVENUE_AMOUNT — derived from RAW_COST when REVENUE_DISTRIBUTED_FLAG is 'Y', or from GMS_BURDEN_COMPONENTS amounts where EVENT_TYPE is 'REVENUE'.
  • BILLED_AMOUNT — derived from RAW_COST when BILLED_FLAG is 'Y', or from burden component amounts where EVENT_TYPE is 'INVOICE'. This column is the primary target of searches for billed amount information against awards.
  • RAW_COST, BURDENED_COST — raw cost originates from GMS_AWARD_DISTRIBUTIONS; burdened cost from PA_COST_DISTRIBUTION_LINES_ALL. Both are zeroed in the burden component branch of the union.
  • PA_DATE, GL_DATE — accounting and general ledger dates carried from the cost distribution lines.
  • INVOICE_ID — numeric conversion of SYSTEM_REFERENCE2, linking the row to the associated invoice where present.

Common Use Cases and Queries

Typical usage includes award status reporting, reconciliation of billed versus revenue-recognized amounts, and drill-down from grant status inquiries to expenditure item detail. A representative query retrieving billed amounts by award is:

  • SELECT award_id, project_id, task_id, expenditure_item_id, billed_amount, revenue_amount, raw_cost, burdened_cost FROM apps.gms_status_actuals_base1_v WHERE award_id = :p_award_id ORDER BY project_id, task_id, expenditure_item_id;
  • SELECT award_id, SUM(billed_amount) billed_total FROM apps.gms_status_actuals_base1_v GROUP BY award_id;
  • SELECT invoice_id, SUM(billed_amount) FROM apps.gms_status_actuals_base1_v WHERE invoice_id IS NOT NULL GROUP BY invoice_id;

Because the view performs aggregation and a UNION ALL internally, queries should restrict by AWARD_ID or date range where possible to limit execution cost. The BILLED_AMOUNT and REVENUE_AMOUNT columns reflect flag-based derivation rather than direct invoice line amounts, so reconciliation against AR invoice lines should account for this distinction.