Search Results unbilled_item_amount




Overview
Explore in AI Chat

Overview

PA_PROJ_UNBILLED_SUMMARY_VIEW is an APPS-owned database view within the Oracle E-Business Suite Projects (PA) module. Its documented purpose is to summarize unbilled and billing hold amounts for projects. The view is a single-row-per-project aggregation layer that condenses the detail-oriented base view into rolled-up figures keyed on PROJECT_ID. It is a published, VALID dictionary object in both release 12.1.1 and 12.2.2, and is referenced by reporting components and integration extracts that need project-level unbilled balances without traversing event or distribution line detail.

The view limits its output to one row per project by applying a GROUP BY PROJECT_ID clause over eight SUM aggregates and two MAX aggregates. Because it performs no joins to project, customer, or currency master tables, it serves as a computational foundation that callers combine with other project or currency views. This design keeps the view lightweight and appropriate for dashboard queries, unbilled balance reconciliation reports, and feeder programs that push unbilled figures into downstream systems.

Underlying Base Objects

The view is defined exclusively over PA_PROJ_UNBILLED_BASE_VIEW, which is documented as its sole referenced base object. The definition is:

All filtering, distribution-level detail, and transactional currency context reside in the base view. The summary view therefore inherits its row population and its currency-dependent columns from that source, and any change to the base view's logic propagates directly to this object.

Key Columns

The fifteen projected columns carry the following meanings:

  • PROJECT_ID — the grouping key and the only non-aggregated column; joins to PA_PROJECTS_ALL.PROJECT_ID.
  • UNBILLED_ITEM_AMOUNT / UNBILLED_ITEM_QUANTITY — summed unbilled item amounts and quantities for the project.
  • ON_HOLD_AMOUNT / ON_HOLD_QUANTITY — summed amounts and quantities placed on billing hold.
  • ONE_TIME_HOLD_AMOUNT / ONE_TIME_HOLD_QUANTITY — summed one-time hold amounts and quantities.
  • UNBILLED_EVENT_AMOUNT — summed unbilled amounts attributable to events.
  • ITEM_CURRENCY_COUNT — count of item-currency rows contributing to the project's totals.
  • ITEM_CURRENCY_CODE — MAX of the item currency code; meaningful only when the count is one.
  • UB_ITEM_BILL_TRANS_AMOUNT — summed bill transaction amount for unbilled items.
  • ON_HOLD_BILL_TRANS_AMOUNT — summed bill transaction amount for held items.
  • ONETIME_HLD_BILL_TRANS_AMOUNT — summed bill transaction amount for one-time holds.
  • EVENT_CURRENCY_COUNT — count of event-currency rows contributing to event totals.
  • EVENT_CURRENCY_CODE — MAX of the event currency code; again interpretable only when EVENT_CURRENCY_COUNT equals one.

A search for event_currency_code resolves to the EVENT_CURRENCY_CODE column, the project-level roll-up of event currencies. Because it is a MAX aggregate, it does not represent a mixed-currency project; consumers should test EVENT_CURRENCY_COUNT to confirm the project's unbilled events are single-currency before using the code in arithmetic or reporting.

Common Use Cases and Queries

Typical scenarios include project unbilled balance summaries, billing hold exception reporting, and currency validation of unbilled event populations.

  • Single-currency event verification: SELECT PROJECT_ID FROM PA_PROJ_UNBILLED_SUMMARY_VIEW WHERE EVENT_CURRENCY_CODE = 'USD' AND EVENT_CURRENCY_COUNT = 1;
  • Project unbilled totals joined to project attributes: SELECT p.segment1, p.name, v.UNBILLED_ITEM_AMOUNT, v.ON_HOLD_AMOUNT FROM PA_PROJ_UNBILLED_SUMMARY_VIEW v, PA_PROJECTS_ALL p WHERE v.PROJECT_ID = p.PROJECT_ID;
  • Mixed-currency identification: SELECT PROJECT_ID, EVENT_CURRENCY_COUNT FROM PA_PROJ_UNBILLED_SUMMARY_VIEW WHERE EVENT_CURRENCY_COUNT > 1;
  • Hold exposure by project: SELECT PROJECT_ID, ON_HOLD_AMOUNT + ONE_TIME_HOLD_AMOUNT hold_total FROM PA_PROJ_UNBILLED_SUMMARY_VIEW ORDER BY hold_total DESC;

Queries against this view execute as APPS and are subject only to the security applied by the calling report or concurrent program. Because currency codes are MAX values over the underlying base view, joins to currency master data should always be guarded by the corresponding count columns.