Search Results year_number




Overview
Explore in AI Chat

Overview

FPA_AW_YEAR_ATTS_V is an Oracle EBS Applications (APPS) view within the Enterprise Territory Management (ETRM) / Fusion Predictive Analytics schema, exposed under the FPA_ prefix. Its structural signature is that of an OLAP metadata bridge rather than a conventional relational view: it resolves a set of year-level (fiscal or calendar) dimension attributes from an Oracle OLAP analytic workspace into a flat, SQL-queryable result set that Forms, concurrent programs, and BI Publisher reports can consume directly.

In Oracle EBS 12.1.1 and 12.2.2, territory and analytics components do not query the analytic workspace directly. They call views such as this one, which invoke the OLAP_TABLE table function against a workspace resolved at runtime through FPA_UTILITIES_PVT.AW_SPACE_NAME. The view therefore acts as a controlled, version-neutral interface between the OLAP engine and the relational application layer. The salient characteristic is that its result set is a duration query: it returns one row per year in the workspace timeline, together with the numeric year identifier that downstream territory and analytics logic requires for arithmetic and comparison.

Underlying Base Objects

The ETRM metadata documents two referenced base objects:

  • FPA_UTILITIES_PVT (package) — supplies the AW_SPACE_NAME constant, which resolves the physical analytic workspace name. Because the view concatenates this value with the literal ' DURATION QUERY', the workspace is not hard-coded and can differ across environments, patches, and releases.
  • OLAP_TABLE (synonym) — the Oracle OLAP table function invocation. It maps the workspace dimension and attribute objects into a relational row source, using the workspace's own aggregation and selection language.

The OLAP_TABLE call inside the view body specifies the storage specification FPA_YEAR_TBL and the limit map 'CALL LMT_YEAR_PRG(''YEAR'')'. The OLAP command further declares the dimension CAL_HIER from CAL_PERIOD_TYPE_D, the dimension YEAR from TIME_D with the attribute YEAR_NUMBER from TIME_TIME_IDENTIFIER_R, and the attribute START_DATE from START_DATE_YEAR_R. These workspace-side analytic objects are not ordinary EBS base tables; they reside in the OLAP workspace and are surfaced only through this view. Consequently, the view is dependent on the workspace being built and refreshed by the ETRM load process — if the workspace is unavailable or stale, the view returns no rows or fails to compile.

Key Columns

The view projects exactly four columns:

  • CAL_HIER — the calendar hierarchy identifier, sourced from the CAL_PERIOD_TYPE_D dimension. It distinguishes which period/calendar structure the row belongs to, which matters where multiple hierarchies coexist in the workspace.
  • YEAR — the year member of the TIME_D dimension, presented at year granularity. This is the principal grouping key for time-series analytics and territory performance roll-ups.
  • YEAR_NUMBER — the numeric year identifier, delivered as a TIME_TIME_IDENTIFIER_R attribute. This is the column users search for. It supports numeric ordering, range filters, and arithmetic in queries where the textual YEAR label is unsuitable.
  • START_DATE — the beginning date of the year, from the START_DATE_YEAR_R attribute. It enables date-range joins against transactional tables and date-effective comparisons.

Common Use Cases and Queries

Typical uses include populating year selection lists in territory administration, driving analytic comparisons that require the numeric year key, and aligning transaction dates to year buckets. A representative query listing available years is:

SELECT year, year_number, start_date FROM apps.fpa_aw_year_atts_v ORDER BY year_number;

Filtering to a specific hierarchy and recent years, for period-aware reporting, follows the pattern:

SELECT year, year_number, start_date FROM apps.fpa_aw_year_atts_v WHERE cal_hier = :p_cal_hier AND year_number >= :p_from_year ORDER BY year_number;

Joining to a transactional fact table on the year boundary supports date-effective lookups. Because the view performs no filtering or predicate pushdown into the workspace, restricting cal_hier and year_number in the WHERE clause remains important for performance, and the OLAP workspace must be current for results to be meaningful.