Search Results effective_period_num

Overview

PA_REP_PERIODS_V is an APPS-owned reporting view in the Oracle E-Business Suite Projects (PA) module. Its documented description states that it "displays the type of the period, year, name." More precisely, it presents a consolidated, uniform list of reporting periods drawn from multiple period calendars within an EBS instance, allowing consumers to enumerate period names, period types, calendar years, and the associated period status without needing to understand the distinct underlying storage of each source. The view is a reporting and integration construct rather than a transactional entity; it exposes read-only data and is not maintained directly. Its value lies in abstracting away the differences between Projects period calendars, General Ledger (GL) period calendars, and other period constructs so that a single query can return a coherent period list. Because one of the exposed columns is PERIOD_STATUS, it is a natural target for the "period_status" search term, which typically seeks the open, closed, or future state of a period.

Underlying Base Objects

The view is defined as a UNION of several period sources. The first branch selects from PA_PERIODS_V (aliased PAPERIOD), contributing the PA period type, period year, period name, quarter number, the PA status meaning and status code, PA start and end dates, and ORG_ID. The second branch joins PA_IMPLEMENTATIONS to GL_SETS_OF_BOOKS, GL_PERIODS, GL_PERIOD_STATUSES, GL_LOOKUPS, and GL_DATE_PERIOD_MAP, and derives the GL period type with its status meaning and code. The third and subsequent branches (for the QR and related period types) build on PA_REP_QUARTER_GL_V and PA_REP_YEAR_CAL_V, with PA_REP_SEQ_NUMBER and PA_LOOKUPS involved in sequencing and lookup resolution. It also references the PA_PERIOD_PROCESS_PKG package (for the APPLICATION_ID constant) and FND_PROFILE. The documented referenced objects are: FND_PROFILE, GL_DATE_PERIOD_MAP, GL_LOOKUPS, GL_PERIODS, GL_PERIOD_STATUSES, GL_SETS_OF_BOOKS, PA_IMPLEMENTATIONS, PA_LOOKUPS, PA_PERIODS_V, PA_PERIOD_PROCESS_PKG, PA_REP_QUARTER_GL_V, PA_REP_SEQ_NUMBER, and PA_REP_YEAR_CAL_V.

Key Columns

• PERIOD_TYPE — identifies the source calendar, for example 'PA', 'GL', or 'QR'.
• PERIOD_YEAR — the calendar year of the period.
• PERIOD_NAME — the period identifier (for example, a month or quarter name).
• MON_OR_QTR — the month or quarter number the period corresponds to.
• GE_WEEK_DT — a reference date; the view text returns SYSDATE for this column.
• PERIOD_STATUS — the human-readable status meaning (for example, Open, Closed, Future). This is the column the "period_status" search targets.
• PERIOD_STATUS_CODE — the code behind the status; GL values derive from the CLOSING_STATUS lookup, restricted to 'C', 'F', 'N', 'O', and 'P'.
• PERIOD_START_DATE / PERIOD_END_DATE — the period's start and end dates.
• EFFECTIVE_PERIOD_NUM — the effective period number; the PA branch returns 0, while the GL branch populates it from GL_PERIOD_STATUSES.
• ORG_ID — the operating unit or organization identifier associated with the row.

Common Use Cases and Queries

The view supports reporting on which periods exist and their current state, which is essential for period-close validation, transaction-date checks, and interface staging. A common pattern filters on status and calendar as follows:

SELECT period_type, period_year, period_name, period_status
FROM   apps.pa_rep_periods_v
WHERE  period_status_code = 'O'
AND    period_name = :p_period_name;

Reporting queries frequently join the view to PA or GL facts on PERIOD_NAME and PERIOD_TYPE to attach status context to summarized amounts. Integration and reconciliation scripts use it to detect closed periods before posting, and cross-calendar listings use it to enumerate the same year across 'PA', 'GL', and 'QR' types. Because the view unions multiple sources, always constrain on PERIOD_TYPE and, where relevant, ORG_ID to keep result sets predictable.