Search Results jai_fa_ast_years




Overview
Explore in AI Chat

Overview

JAI_FA_AST_YEARS is a reference table owned by the JA schema within the Asia/Pacific Localizations product family of Oracle E-Business Suite. Its documented purpose is to store the year start date and year end date for periods against which fixed asset and related period rates are defined. In practice, the table acts as a lightweight calendar-year dimension that localizations and downstream reporting objects use to bucket financial, inventory, and spend data by fiscal or calendar year.

From a Data Vault modeling perspective, the metadata classifies this object as standalone, meaning the heuristics mined from the foreign-key structure do not identify it as either a hub or a link. Nonetheless, the presence of a surrogate primary key (YEAR_ID) and two descriptive business attributes (YEAR_START and YEAR_END) makes JAI_FA_AST_YEARS a natural candidate for a small reference or hub-style dimension, with the date ranges functioning as the descriptive satellite attributes. Its role is largely supporting: it supplies a stable YEAR_ID that many localized materialized views and staging tables reference for time-based aggregation.

Key Information Stored

The table contains nine documented columns, and the most important of these are described below. The surrogate primary key is YEAR_ID, enforced through the unique index JAI_FA_AST_YEARS_PK. A second unique index, JAI_FA_AST_YEARS_UK1, covers YEAR_START and YEAR_END, designating these two columns as the business-key candidates that uniquely identify a year by its boundaries.

  • YEAR_ID — Surrogate primary key; the value referenced by all downstream foreign keys.
  • YEAR_START — Start date of the year; part of the business-key unique index.
  • YEAR_END — End date of the year; part of the business-key unique index.
  • CREATED_BY, CREATION_DATE — Standard Oracle audit columns recording row creation.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard audit columns recording the most recent modification.
  • LAST_UPDATE_LOGIN — Login identifier associated with the last update.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Applications framework.

Common Use Cases and Queries

The primary use case is time bucketing. Because YEAR_ID is a compact surrogate, it is widely joined into reporting and materialized-view queries to resolve a year's boundaries without repeating date literals. A typical lookup pattern is:

SELECT year_id, year_start, year_end
FROM   ja.jai_fa_ast_years
WHERE  :p_date BETWEEN year_start AND year_end;

Reporting scenarios include fixed-asset period-rate reporting for APAC localizations, fiscal-year roll-ups in inventory valuation summaries, and spend-analysis staging tables that need a governed year key. Analysts commonly join JAI_FA_AST_YEARS to a fact or materialized view on YEAR_ID, for example:

SELECT v.year_id, y.year_start, y.year_end, SUM(v.amount)
FROM   opi_inv_val_sum_mv v,
       ja.jai_fa_ast_years y
WHERE  v.year_id = y.year_id
GROUP  BY v.year_id, y.year_start, y.year_end
ORDER  BY y.year_start;

Because the table is small and changes infrequently, it is well suited to being cached or used as a lookup dimension in BI Publisher reports and Discoverer workbooks that must present data by year.

Related Objects

A large number of staging tables, materialized views, and dimensions reference JAI_FA_AST_YEARS through the YEAR_ID column. The most significant are listed below with their join columns.

These relationships demonstrate that YEAR_ID is the operative integration point; any change to the year boundaries in JAI_FA_AST_YEARS propagates semantically to every dependent view that aggregates by year.