Results for “pa_rep_year_gl_v”

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

Overview

PA_REP_YEAR_GL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered under the Projects (PA) product family. Its documented description is concise but precise: it is a view used to retrieve General Ledger period year numbers. It exposes a distinct list of fiscal/accounting years derived from the GL period calendar associated with the set of books configured for a given Projects implementation, together with the corresponding operating unit identifier.

The view functions as a lightweight lookup and validation source. Reporting constructs in Oracle Projects frequently require a selection of accounting years — for example, when building parameter lists in concurrent programs, Oracle Reports or BI Publisher layouts, or OAF-based pages that must filter project, expenditure, or revenue data by GL year. Rather than having each report join GL_PERIODS to GL_SETS_OF_BOOKS and PA_IMPLEMENTATIONS independently, PA_REP_YEAR_GL_V packages that join logic once and exposes only the two attributes ordinarily needed: PERIOD_YEAR and ORG_ID. Because the view is defined with SELECT DISTINCT, it returns each year once per operating unit, which makes it suitable for use as the source of a list of values (LOV) or a value set definition.

Underlying Base Objects

The view text defines an inner join across three referenced objects, all resolved through the APPS schema:

• PA_IMPLEMENTATIONS — supplies ORG_ID, the operating unit under which the Projects implementation is registered. Only implementations with a populated SET_OF_BOOKS_ID participate in the join.
• GL_SETS_OF_BOOKS — the accounting books definition (documented as a view). It links the Projects implementation to a chart of accounts and, critically, to the PERIOD_SET_NAME and ACCOUNTED_PERIOD_TYPE that govern which GL calendar applies.
• GL_PERIODS — the accounting calendar periods table. It provides PERIOD_YEAR and is joined on both PERIOD_SET_NAME and PERIOD_TYPE, ensuring the years returned come only from the same period set and accounted period type as the implementation's set of books.

The join conditions are: PA_IMPLEMENTATIONS.SET_OF_BOOKS_ID equals GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID; GL_SETS_OF_BOOKS.PERIOD_SET_NAME equals GL_PERIODS.PERIOD_SET_NAME; and GL_SETS_OF_BOOKS.ACCOUNTED_PERIOD_TYPE equals GL_PERIODS.PERIOD_TYPE. No WHERE clause further restricts the result set, so the view returns all years present in the qualifying calendar, for every qualifying implementation.

Key Columns

• PERIOD_YEAR — the accounting year value taken directly from GL_PERIODS.PERIOD_YEAR. This is a numeric fiscal year designation (for example, 2024), not a date. Because GL calendars may be defined with a non-calendar fiscal year end, the value reflects the period set's own year numbering.
• ORG_ID — the operating unit identifier from PA_IMPLEMENTATIONS.ORG_ID. This is the multi-org partitioning attribute and is the column typically constrained when a query must be limited to a single operating unit.

No other columns are exposed. The absence of a set of books or period set column means the view is not directly usable to distinguish between multiple calendars unless ORG_ID happens to map one-to-one to them.

Common Use Cases and Queries

Typical applications include populating a year parameter at the start of a Projects report, validating a user-entered year, and driving year-over-year comparative reporting. The following query returns all years for a specific operating unit, ordered for presentation:

• SELECT period_year FROM apps.pa_rep_year_gl_v WHERE org_id = :p_org_id ORDER BY period_year DESC;
• SELECT DISTINCT period_year FROM apps.pa_rep_year_gl_v ORDER BY 1; — builds a master list of accounting years across all implementations.
• SELECT org_id, MIN(period_year), MAX(period_year) FROM apps.pa_rep_year_gl_v GROUP BY org_id; — reports the span of years available per operating unit.
• SELECT COUNT(*) FROM apps.pa_rep_year_gl_v WHERE org_id = :p_org_id AND period_year = :p_year; — validates a submitted year before processing.

Because the view joins GL tables that can be large, queries should filter on ORG_ID where possible, and the underlying GL_PERIODS access should be supported by its standard period set and period type indexes.