Search Results line_item_cost




Overview
Explore in AI Chat

Overview

IGW_EXPENDITURE_TYPE_SUM_V is a reporting view in the Oracle E-Business Suite Grants Management (IGW) schema, owned by APPS and exposed through the IGW application. Its purpose is to present budget line item cost amounts summarized by expenditure type across a rolling four-period window, pivoting the period dimension horizontally into the columns P1_AMT, P2_AMT, P3_AMT, and P4_AMT. The view is central to award and proposal budget reporting where costs must be displayed by expenditure category over consecutive budget periods rather than as a single flat list.

The view does not hold data itself. It is a UNION ALL-style construct over IGW_BUDGET_DETAILS that resolves the current proposal, version, and budget period identifiers dynamically through the PL/SQL package function IGW_BUDGET_OPERATIONS (GET_PROPOSAL_ID, GET_VERSION_ID, GET_PERIOD_ID). This means the view is context-sensitive: the rows returned depend on the session-level state maintained by the IGW budget operations package, typically established when the user navigates to a budget or expenditure type summary page in the Grants accounting forms or OAF pages.

Underlying Base Objects

The documented base object is IGW_BUDGET_DETAILS, the transaction table that stores individual budget line records for proposals, versions, and periods. The view is defined as four SELECT statements joined by UNION, each reading the same base table but applying a different budget_period_id offset:

  • First branch: budget_period_id = GET_PERIOD_ID, mapping line_item_cost to P1_AMT.
  • Second branch: budget_period_id = GET_PERIOD_ID + 1, mapping line_item_cost to P2_AMT.
  • Third branch: budget_period_id = GET_PERIOD_ID + 2, mapping line_item_cost to P3_AMT.
  • Fourth branch: budget_period_id = GET_PERIOD_ID + 3, mapping line_item_cost to P4_AMT.

Each branch projects expenditure_type, the four amount columns (only one populated with line_item_cost, the remainder set to TO_NUMBER(TO_NUMBER(NULL)), effectively a null numeric), and a constant rate_class_id of 0. The filter predicate consistently constrains proposal_id, version_id, and budget_period_id through the IGW_BUDGET_OPERATIONS package functions. No additional documented base objects are referenced, so the view's dependency is effectively limited to IGW_BUDGET_DETAILS plus the package calls.

Key Columns

  • EXPENDITURE_TYPE — The expenditure category (for example, labor, material, travel) used to group costs for reporting.
  • P1_AMT — line_item_cost for the current budget period (GET_PERIOD_ID).
  • P2_AMT — line_item_cost for the next budget period (GET_PERIOD_ID + 1).
  • P3_AMT — line_item_cost for the second subsequent period (GET_PERIOD_ID + 2).
  • P4_AMT — line_item_cost for the third subsequent period (GET_PERIOD_ID + 3).
  • RATE_CLASS_ID — A constant zero literal, used as a placeholder to satisfy the consuming report's expected column structure rather than a meaningful rate classification.

Because each UNION branch populates only one of the P-series amount columns, consumers typically aggregate by expenditure_type to consolidate the four rows per expenditure type into a single row spanning the four-period horizon.

Common Use Cases and Queries

The view supports expenditure type summaries on grant budget inquiry pages and custom reports that present a four-period cost profile. Typical uses include award budget versus actual comparisons, expenditure category roll-ups for sponsor reporting, and period-over-period budget trend analysis. A representative query is:

  • SELECT expenditure_type, SUM(P1_AMT) P1, SUM(P2_AMT) P2, SUM(P3_AMT) P3, SUM(P4_AMT) P4 FROM IGW_EXPENDITURE_TYPE_SUM_V GROUP BY expenditure_type ORDER BY expenditure_type;
  • SELECT expenditure_type, NVL(P1_AMT,0) + NVL(P2_AMT,0) + NVL(P3_AMT,0) + NVL(P4_AMT,0) total_cost FROM IGW_EXPENDITURE_TYPE_SUM_V;

Because results depend on the IGW_BUDGET_OPERATIONS package state, programmatic callers must ensure the correct proposal, version, and period context is set before querying the view, otherwise the GET_PERIOD_ID offset will return unintended periods or no rows.