Search Results mon_or_qtr

Overview

APPS.PA_REP_PERIODS_V is an Oracle E-Business Suite reporting view that consolidates period definitions from multiple Oracle Projects and General Ledger sources into a single, unified period calendar. It presents Project Accounting (PA) periods, General Ledger (GL) accounting periods, and derived Project Reporting quarters side by side, enabling reports and concurrent programs to resolve valid accounting and reporting periods without querying each source independently. The view is widely referenced by Oracle Projects reporting components and is central to period-driven filtering in both the 12.1.1 and 12.2.2 releases.

The view is defined as a UNION of three SELECT statements, each producing a distinct period_type value: 'PA' for Project Accounting periods, 'GL' for General Ledger periods, and 'QR' for reporting quarters. Because it is a view rather than a table, it carries no storage of its own; all data is derived at query time.

Underlying Base Objects

The PA branch reads from PA_PERIODS_V, exposing the Project Accounting period name, year, quarter number, status meaning and code, start and end dates, and operating unit. The GL branch performs a multi-table join across PA_IMPLEMENTATIONS, GL_SETS_OF_BOOKS, GL_PERIODS, GL_PERIOD_STATUSES, GL_LOOKUPS, and GL_DATE_PERIOD_MAP. This join resolves the set of books assigned in Project Accounting implementation options, maps the accounted period type, and restricts closing statuses to lookup codes C, F, N, O, and P under lookup type CLOSING_STATUS. The package PA_PERIOD_PROCESS_PKG supplies the application identifier used for GL period status filtering. The quarter branch derives its rows from PA_REP_QUARTER_GL_V, PA_REP_YEAR_CAL_V, PA_LOOKUPS, and PA_REP_SEQ_NUMBER, constructing quarter descriptions from year and quarter number.

Key Columns

• PERIOD_TYPE — Discriminator identifying the source: PA, GL, or QR.
• PERIOD_YEAR / PERIOD_NAME — The year and display name of the accounting or reporting period.
• MON_OR_QTR — For PA and GL rows this holds the quarter number; for quarter rows it holds the quarter ordinal.
• GE_WEEK_DT — Populated with SYSDATE across all branches; effectively the current date used as a reference point for Gregorian/ISO week alignment in period reporting logic.
• PERIOD_STATUS / PERIOD_STATUS_CODE — The translated meaning and underlying lookup code for PA and GL periods; blank for quarter rows.
• PERIOD_START_DATE / PERIOD_END_DATE — The effective date boundaries of the period.
• EFFECTIVE_PERIOD_NUM — GL only; the effective period number from GL_PERIOD_STATUSES. PA rows carry 0.
• ORG_ID — The operating unit context, sourced from PA_PERIODS_V or PA_IMPLEMENTATIONS.

Common Use Cases and Queries

Typical uses include populating period selection lists in Projects reporting, validating that a GL period is open before expenditure or billing processing, and aligning PA reporting periods to their GL counterparts for reconciliation.

Retrieving open GL periods for the current year:

SELECT period_name, period_start_date, period_end_date
FROM   apps.pa_rep_periods_v
WHERE  period_type = 'GL'
AND    period_status_code = 'O'
AND    period_year = TO_CHAR(SYSDATE,'YYYY');

Comparing PA and GL periods by name for reconciliation:

SELECT p.period_name, p.period_status, g.period_status
FROM   apps.pa_rep_periods_v p,
       apps.pa_rep_periods_v g
WHERE  p.period_type = 'PA'
AND    g.period_type = 'GL'
AND    p.period_name = g.period_name;

Because the view depends on joins to GL and PA setup, queries should be filtered by PERIOD_TYPE and ORG_ID where possible to limit the row set returned by the UNION.