Search Results gl_periods_n2

Overview

GL.GL_PERIODS is the master reference table for accounting periods within the Oracle E-Business Suite General Ledger module, stored in the GL schema and delivered under FND Design Data SQLGL. Each row represents a single accounting period belonging to an accounting calendar, capturing the start and end dates, the fiscal year and period number, the period type, quarter information, and descriptive attributes. The table is central to period-based processing across the entire E-Business Suite: subledger accounting, journal entry validation, period-close routines, and reporting all resolve dates to periods by querying this table.

Structurally, GL_PERIODS maintains a one-to-many relationship with GL_PERIOD_SETS, where a period set (accounting calendar) owns many individual periods. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, while its indexes are held in APPS_TS_TX_IDX. Its documented physical schema at 12.2.2 comprises 28 columns, including the standard Who columns and a ZD_EDITION_NAME column supporting edition-based redefinition. Under a heuristic Data Vault classification mined from the foreign key structure, GL_PERIODS behaves as a hub — a durable set of business keys (period names within a calendar) that downstream transactional and status tables reference rather than a transactional link or a mutable satellite.

Key Information Stored

The declared primary key is GL_PERIODS_PK, composed of PERIOD_SET_NAME and PERIOD_NAME. Both are mandatory VARCHAR2(15) columns: PERIOD_SET_NAME holds the accounting calendar name, and PERIOD_NAME holds the system-generated period label (for example, JAN-24). These act as the surrogate/business composite key for direct lookups and joins.

Two unique indexes define additional business-key candidates. GL_PERIODS_U1 (PERIOD_NAME, PERIOD_SET_NAME, ZD_EDITION_NAME) enforces uniqueness of a period name within a calendar, and is the index most commonly consulted when queries filter by period name. GL_PERIODS_U2 (PERIOD_SET_NAME, PERIOD_TYPE, PERIOD_YEAR, PERIOD_NUM, ZD_EDITION_NAME) enforces uniqueness of the period sequence within a calendar and type. Two non-unique indexes, GL_PERIODS_N1 on START_DATE and GL_PERIODS_N2 on END_DATE, support date-range resolution.

• PERIOD_SET_NAME — accounting calendar the period belongs to.
• PERIOD_NAME — user-facing period label; primary lookup key.
• START_DATE / END_DATE — the inclusive boundaries of the accounting period.
• YEAR_START_DATE / QUARTER_START_DATE — anchors for the fiscal year and quarter containing the period.
• PERIOD_TYPE — accounting period type (for example, Month, Quarter, Year); foreign-keyed to GL_PERIOD_TYPES.
• PERIOD_YEAR / PERIOD_NUM — fiscal year and sequence number used for ordering and roll-ups.
• QUARTER_NUM — quarter number of the period.
• ENTERED_PERIOD_NAME — the period name as entered by the user, useful where display and stored names differ.
• ADJUSTMENT_PERIOD_FLAG — indicates whether the period is an adjustment period.
• DESCRIPTION — VARCHAR2(240) free-text description.
• Who columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The most frequent pattern resolves a transaction date to its containing accounting period. The START_DATE and END_DATE columns, backed by GL_PERIODS_N1 and GL_PERIODS_N2, make this efficient:

• Period for a date: SELECT period_name FROM gl_periods WHERE period_set_name = :calendar AND :txn_date BETWEEN start_date AND end_date.
• All periods in a year: filter by PERIOD_SET_NAME and PERIOD_YEAR, ordered by PERIOD_NUM.
• Period metadata lookup: join on (PERIOD_SET_NAME, PERIOD_NAME) to enrich journal lines or subledger data with fiscal year, quarter, and start/end dates.
• Adjustment period reporting: filter on ADJUSTMENT_PERIOD_FLAG to isolate closing and adjustment periods.
• Date-range analytics: use YEAR_START_DATE and QUARTER_START_DATE to build fiscal calendars for BI Publisher or OTBI reporting.

A typical reporting join pattern is gl_periods p JOIN gl_period_sets s ON p.period_set_name = s.period_set_name JOIN gl_period_types t ON p.period_type = t.period_type, which yields a fully described calendar suitable for date-dimension tables.

Related Objects

• GL.GL_PERIOD_SETS — parent calendar table; joined on PERIOD_SET_NAME.
• GL.GL_PERIOD_TYPES — referenced by PERIOD_TYPE; defines period type attributes.
• ORG_ACCT_PERIODS — Inventory organization accounting periods, referencing this table by PERIOD_SET_NAME; drives inventory period close.
• CST_PAC_PERIODS — Cost Management periodic average cost periods, referencing PERIOD_SET_NAME.
• CN_PERIOD_STATUSES_ALL — period status (open/closed) per ledger, referencing PERIOD_SET_NAME; frequently joined with GL_PERIODS to report open periods.
• IGC_CC_PERIODS and IGC_CBC_JE_LINES — Grants Management period and journal line tables referencing PERIOD_SET_NAME.
• AMW_CERTIFICATION_B — certification periods referencing CERTIFICATION_PERIOD_SET_NAME.
• AS_MC_SALES_CREDITS_DEN — sales credit conversion periods referencing CONVERSION_PERIOD_SET_NAME.

These dependencies confirm GL_PERIODS as a foundational hub: period keys defined here propagate into subledger, costing, grants, and reporting structures throughout the E-Business Suite.