Results for “gl_transaction_dates”

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

Overview

GL_TRANSACTION_DATES is a General Ledger reference table in Oracle E-Business Suite (validated in 12.1.1 and 12.2.2) that stores the individual calendar days belonging to each transaction calendar defined in GL_TRANSACTION_CALENDAR. Its documented purpose is to distinguish business days from non-business days for transaction calendars, a function that underpins period-close scheduling, subledger accounting date validation, and any process that must determine whether a given date is an eligible posting or activity date.

Each row represents exactly one calendar day within one transaction calendar. The table owns a composite primary key, GL_TRANSACTION_DATES_PK, defined on (TRANSACTION_CALENDAR_ID, TRANSACTION_DATE), which uniquely identifies a single day within a calendar. A unique index, GL_TRANSACTION_DATES_U1, mirrors the same column pair and confirms the business key. The single documented foreign key, TRANSACTION_CALENDAR_ID to GL_TRANSACTION_CALENDAR, establishes the parent-child dependency. Applying Data Vault modeling heuristics to this FK structure, the table is satellite-leaning: it hangs off the GL_TRANSACTION_CALENDAR parent and carries descriptive, time-versioned attributes (the business-day flag and day-of-week) rather than acting as an independent hub or a connecting link. Column-count documentation notes 25 physical columns, the majority of which are the standard EBS WHO columns and the CONTEXT/ATTRIBUTE1–15 flexfield set.

Key Information Stored

The most significant columns are:

• TRANSACTION_CALENDAR_ID — the foreign key to GL_TRANSACTION_CALENDAR and the first component of both the primary key and the unique index. It identifies which transaction calendar a given day belongs to.
• TRANSACTION_DATE — the calendar date itself and the second component of the composite primary key and unique index. The pair (TRANSACTION_CALENDAR_ID, TRANSACTION_DATE) is the documented business-key candidate.
• BUSINESS_DAY_FLAG — the core descriptive attribute, indicating whether the date is a business day or a non-business day within that calendar. This flag drives date-eligibility logic in General Ledger and subledger flows.
• DAY_OF_WEEK — the day-of-week classification for the date, useful for reporting and for deriving patterns of business versus non-business days.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns recording who created and last modified each row and when.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — the standard EBS descriptive flexfield and context columns, available for customer-specific extension.

Common Use Cases and Queries

Typical scenarios include determining whether a transaction date is an eligible business day, validating a proposed accounting date against a calendar, and reporting the distribution of business versus non-business days for a calendar year.

• List all business days for a calendar within a date range:
  SELECT transaction_date FROM gl_transaction_dates WHERE transaction_calendar_id = :cal AND business_day_flag = 'Y' AND transaction_date BETWEEN :from_dt AND :to_dt ORDER BY transaction_date;
• Confirm whether a specific date is a business day:
  SELECT business_day_flag FROM gl_transaction_dates WHERE transaction_calendar_id = :cal AND transaction_date = :dt;
• Count non-business days per day of week for a calendar:
  SELECT day_of_week, COUNT(*) FROM gl_transaction_dates WHERE transaction_calendar_id = :cal AND business_day_flag = 'N' GROUP BY day_of_week;
• Join to the parent calendar to resolve the calendar name:
  SELECT d.transaction_date, d.business_day_flag FROM gl_transaction_dates d, gl_transaction_calendar c WHERE d.transaction_calendar_id = c.transaction_calendar_id AND c.name = :name;

Related Objects

The principal related objects, grounded in the documented FK/PK structure, are:

• GL_TRANSACTION_CALENDAR — the parent table. Join on GL_TRANSACTION_DATES.TRANSACTION_CALENDAR_ID = GL_TRANSACTION_CALENDAR.TRANSACTION_CALENDAR_ID to resolve calendar header information.
• GL_TRANSACTION_DATES_PK / GL_TRANSACTION_DATES_U1 — the primary key constraint and unique index on (TRANSACTION_CALENDAR_ID, TRANSACTION_DATE) that enforce row uniqueness.
• GL_PERIODS / GL_LEDGERS — accounting calendars and ledgers that consume transaction-calendar business-day logic for period and date validation.
• GL_DAILY_RATES — daily conversion rates often aligned to business-day calendars for rate application.
• Subledger validation objects (e.g., AP/AR transaction date validation) — subledger entry flows reference transaction calendars indirectly to determine valid accounting dates.