Results for “fpa_aw_year_atts_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view FPA_AW_YEAR_ATTS_V is an Oracle E-Business Suite database object owned by the APPS schema and registered as VALID within the FPA (Project Portfolio Analysis) product module. It exposes the calendar-year dimension attributes used by Oracle Project Portfolio Analysis analytics. In EBS 12.1.1 and 12.2.2, Project Portfolio Analysis relies on an embedded Oracle OLAP analytic workspace to store and query aggregated project performance measures across time, organizational, and classification dimensions. This view provides the time dimension at the year grain, allowing report pages, dashboards, and concurrent programs in the FPA module to enumerate years, their calendar hierarchy membership, and their start dates without issuing direct OLAP queries against the analytic workspace.

Because the FPA analytics layer is served from an OLAP workspace rather than from conventional relational summary tables, consumer code cannot simply select from a physical fact table. Views such as FPA_AW_YEAR_ATTS_V bridge this gap by presenting OLAP dimension member attributes through a relational interface. Applications and custom reports that must resolve the set of available analysis years—for example, to populate a year list of values on a portfolio analysis page—query this view instead of the workspace directly.

Underlying Base Objects

Per the ETRM metadata for release 12.2.2, the view references two documented base objects:

• FPA_UTILITIES_PVT (PACKAGE) — the FPA utilities package that supplies the analytic workspace name. The view text concatenates FPA_UTILITIES_PVT.AW_SPACE_NAME with the literal ' DURATION QUERY' to construct the workspace identifier passed to OLAP_TABLE.
• OLAP_TABLE (SYNONYM) — the Oracle OLAP table function used to project analytic workspace data into relational rows and columns.

The view is therefore not defined over ordinary base tables. Instead, it invokes the OLAP_TABLE table function with the FPA_YEAR_TBL output type and the OLAP command CALL LMT_YEAR_PRG('YEAR'), then maps workspace dimension members and attributes into the relational columns described below. This dependency on FPA_UTILITIES_PVT means the view resolves correctly only within the FPA application schema context where that package is compiled and the analytic workspace is attached to the session.

Key Columns

The view exposes four documented columns, each corresponding to a year-level attribute in the analytic workspace:

• CAL_HIER — the calendar hierarchy to which the year member belongs. It is sourced as a dimension from CAL_PERIOD_TYPE_D, allowing the same year to be interpreted under different calendar definitions.
• YEAR — the year dimension member from TIME_D, representing the descriptive year label used in analysis.
• YEAR_NUMBER — the numeric year identifier, mapped as an attribute from TIME_TIME_IDENTIFIER_R. This is typically the value used for sorting and for joining to transactional data.
• START_DATE — the start date associated with the year, drawn from the START_DATE_YEAR_R attribute. It anchors the year to the Gregorian date range used by the FPA time dimension.

Common Use Cases and Queries

Typical usage includes populating year selection lists in Project Portfolio Analysis pages, validating that a requested analysis year exists in the workspace, and joining year attributes to project or portfolio summaries for reporting. Because the view depends on the OLAP workspace and FPA_UTILITIES_PVT, calls should be made in the FPA application context.

Basic enumeration of analysis years:

• SELECT YEAR, YEAR_NUMBER, CAL_HIER, START_DATE FROM APPS.FPA_AW_YEAR_ATTS_V ORDER BY YEAR_NUMBER;

Restricting to a single calendar hierarchy:

• SELECT YEAR, YEAR_NUMBER, START_DATE FROM APPS.FPA_AW_YEAR_ATTS_V WHERE CAL_HIER = :p_cal_hier;

Resolving a start date for a specific year label:

• SELECT START_DATE FROM APPS.FPA_AW_YEAR_ATTS_V WHERE YEAR = :p_year;

These queries are appropriate for custom reports, validation routines, and integration extracts that must align external data to the year grain of the FPA analytic workspace without directly manipulating OLAP objects.