Search Results amount_type_code




Overview
Explore in AI Chat

Overview

The view APPS.PA_RPT_PROJECT_PERIOD_AMOUNTS is a reporting-only database object in Oracle EBS Projects (PA). It presents project-level financial amounts — cost and revenue, actual and budget — aggregated by accounting period and by the period accumulation type. It is a defined view over the Projects Business Intelligence System (BIS) drill-down table, and its purpose is to deliver a pre-aggregated, period-oriented result set suitable for period-by-period project reporting, dashboards, and integration extracts in both Oracle EBS 12.1.1 and 12.2.2.

Because the view performs its own aggregation and filtering, it isolates report consumers from the detail-level structure of the underlying BIS table. The composition of the view is significant: it filters the source to a single drill-down dimension and then pivots the generic amount-type column into four distinct measures using DECODE. The result is one row per project, accumulation period type, period name, and start/end date, with cost actual, revenue actual, cost budget, and revenue budget expressed as separate columns. This structure directly supports period-based project performance reporting.

Underlying Base Objects

The view is defined over a single referenced base object: PA_BIS_PRJ_BY_PRD_DRILLS, accessed in the ETRM metadata as a synonym. This object is part of the PA BIS drill-down infrastructure, which stores summarized project amounts in a generalized, drill-down-oriented form. The source object carries a DRILLDOWN_TYPE discriminator and a generic AMOUNT_TYPE_CODE column, meaning a single row can represent a cost or a revenue amount, and actual or budget amounts, depending on the attribute values.

The view definition restricts the source with WHERE DRILLDOWN_TYPE = 'ORGANIZATION', so only the organization-level drill-down records participate. It then groups by PROJECT_ID, ACCUMULATION_PERIOD_TYPE, PERIOD_NAME, START_DATE, and END_DATE. The four measure columns are produced by summing ACTUAL or BUDGET where AMOUNT_TYPE_CODE is 'COST' or 'REVENUE', with NVL(...,0) guarding against nulls. Rows whose amount type is neither cost nor revenue contribute zero to all four measures.

Key Columns

  • PROJECT_ID — The project identifier grouping the reporting amounts.
  • ACCUMULATION_PERIOD_TYPE — The period accumulation category used to organize amounts (for example, accounting period versus another accumulation basis).
  • PERIOD_NAME — The accounting period name to which the aggregated amounts belong.
  • START_DATE / END_DATE — The beginning and end dates of the reporting period.
  • Cost actual (SUM of DECODE on 'COST' and ACTUAL) — Total actual cost for the period.
  • Revenue actual (SUM of DECODE on 'REVENUE' and ACTUAL) — Total actual revenue for the period.
  • Cost budget (SUM of DECODE on 'COST' and BUDGET) — Total budgeted cost for the period.
  • Revenue budget (SUM of DECODE on 'REVENUE' and BUDGET) — Total budgeted revenue for the period.
  • AMOUNT_TYPE_CODE — The source discriminator in the base table, whose documented values 'COST' and 'REVENUE' drive the pivoting; it is not projected as an output column of the view.

Common Use Cases and Queries

The view is typically used to produce period-by-period project cost and revenue reporting, compare actuals against budget, and feed downstream extracts. Because all four measures are pre-aggregated, a consumer can query the view directly without reproducing the decode and group logic.

Example — period amounts for a project:

  • SELECT project_id, period_name, start_date, end_date, cost_actual, revenue_actual, cost_budget, revenue_budget FROM apps.pa_rpt_project_period_amounts WHERE project_id = :p_project_id ORDER BY period_name;

Example — actual versus budget variance by period:

  • SELECT period_name, cost_actual - cost_budget AS cost_variance, revenue_actual - revenue_budget AS revenue_variance FROM apps.pa_rpt_project_period_amounts WHERE project_id = :p_project_id;

Example — cross-project comparison for a period:

  • SELECT project_id, cost_actual, revenue_actual FROM apps.pa_rpt_project_period_amounts WHERE period_name = :p_period ORDER BY project_id;

As the view exposes only organization-level drill-down data, a query that appears to omit amounts likely reflects that the corresponding source rows carry a different DRILLDOWN_TYPE or an AMOUNT_TYPE_CODE outside the cost/revenue set. Join column names to PA_PROJECTS_ALL and GL_PERIODS for descriptive reporting, and treat the object as read-only reporting infrastructure.