The Oracle RMS Retail Calendar

Why Retail Uses Its Own Calendar#

Retail businesses don't operate on calendar months. A calendar month has between 28 and 31 days, which makes year-over-year comparisons meaningless — January 2024 had one more trading day than January 2025, and any sales comparison between the two is distorted before you even start.

Retail solves this with a fiscal calendar that divides the year into equal-length periods based on weeks, not arbitrary month boundaries. The most popular structure in global retail is the 4-5-4 calendar — named for the number of weeks in each month within a quarter: 4 weeks, then 5 weeks, then 4 weeks. Every quarter is 13 weeks, every year is exactly 52 weeks (364 days), and year-over-year period comparisons are always like-for-like.

Oracle RMS is built around this retail calendar at every level. The PERIOD table is one of the most referenced tables in all of RMS. Date-based queries in the stock ledger, purchasing, pricing, and replenishment all join to PERIOD rather than using TRUNC(date, 'MM') or calendar month functions.

Calendar Types — 4-5-4, 4-4-5, and Gregorian#

RMS supports three calendar structures, configured at system setup and fixed for the life of the instance:

The calendar type determines how the PERIOD table is populated and how period boundaries are calculated. Once chosen, it cannot be changed — the entire history of your stock ledger, purchasing records, and financial closes are anchored to it.

The PERIOD Table — The Core Time Dimension#

The PERIOD table is the master time dimension for Oracle RMS. It is pre-populated with periods spanning the full implementation history and several years into the future. Every date in RMS can be looked up against PERIOD to find which fiscal week, month, quarter, and year it falls in.

Period Hierarchy — Week, Month, Quarter, Year#

The PERIOD table is self-referencing via PARENT_PERIOD_ID. The hierarchy flows upward:

To find which fiscal month a specific date falls in, you join from the week-level period (found by matching START_DATE ≤ date ≤ END_DATE) up to its parent month via PARENT_PERIOD_ID.

The SYSDATE Batch — Advancing RMS Time#

Oracle RMS does not use Oracle's built-in SYSDATE function to determine "today" for business operations. Instead, it maintains its own virtual date in SYSTEM_OPTIONS.VDATE. The SYSDATE batch job (also called RMSDATE at some retailers) advances this virtual date by one day each time it runs.

This design has important implications for developers:

1. Batch sequencing is time-sensitive. Any batch that runs before the SYSDATE batch on a given day will process against yesterday's VDATE. Any batch that runs after it will process against today's. If SYSDATE doesn't run, no batch for that day will process against the correct date — every date-based calculation will be one day behind.

2. Custom logic must read VDATE, not SYSDATE. Custom PL/SQL in RMS should always read the current date from SYSTEM_OPTIONS.VDATE rather than calling Oracle's SYSDATE. In production these will agree most of the time, but in test environments, the VDATE is often deliberately set to a past or future date for testing.

3. VDATE advancement triggers period boundaries. When the SYSDATE batch advances VDATE across a period boundary (e.g., from the last day of a fiscal week to the first day of the next), it triggers all period-boundary processing — the stock ledger snapshot for the closing week, replenishment period recalculations, and more.

-- Always read the RMS virtual date this way in custom code
SELECT vdate AS rms_current_date
FROM   system_options;
-- DO NOT use this in RMS business logic:
-- SELECT TRUNC(SYSDATE) FROM DUAL;
-- SYSDATE and VDATE may differ in test/staging environments

Period Close & the Stock Ledger#

At the end of every fiscal week, RMS's period-close batch takes a snapshot of the stock ledger — recording opening stock, sales, purchases, adjustments, and closing stock for every item at every location for that fiscal period. Once a period is closed (CLOSED_IND = 'Y' on the PERIOD row), the historical snapshot is immutable.

This has direct implications for custom development:

• Never query STKLEDGR (the stock ledger) for a closed period expecting to find the "current" position — use ITEM_LOC_SOH for current stock on hand.
• Reprocessing or backdating inventory adjustments into a closed period requires either a period re-open (a controlled DBA operation) or a bridging adjustment in the current open period.
• Financial reporting should always join to PERIOD and filter by CLOSED_IND = 'Y' to ensure only finalized data is included.

Key Tables — Quick Reference#

Practical SQL Examples#

1. Find the current fiscal week, month, and year

-- Current RMS period context — week, month, quarter, year
WITH current_date_cte AS (
SELECT vdate AS rms_date FROM system_options
)
SELECT wk.period_id        week_id,
wk.period_desc      fiscal_week,
wk.fiscal_week      week_no_in_month,
mo.period_desc      fiscal_month,
mo.fiscal_month     month_no_in_year,
qr.period_desc      fiscal_quarter,
yr.period_desc      fiscal_year,
yr.fiscal_year      year_number
FROM   period wk
JOIN   period mo  ON mo.period_id = wk.parent_period_id
JOIN   period qr  ON qr.period_id = mo.parent_period_id
JOIN   period yr  ON yr.period_id = qr.parent_period_id
WHERE  wk.period_type = 'WK'
AND  (SELECT rms_date FROM current_date_cte)
BETWEEN wk.start_date AND wk.end_date;

2. Stock ledger summary for a closed fiscal month

-- Stock ledger for a specific fiscal month, by department
SELECT dp.dept,
dp.dept_name,
SUM(sl.opening_stock_cost)  opening_cost,
SUM(sl.net_sales_cost)      sales_cost,
SUM(sl.purchases_cost)      purchases_cost,
SUM(sl.closing_stock_cost)  closing_cost
FROM   stkledgr   sl
JOIN   period     wk   ON  wk.period_id   = sl.period_id
JOIN   period     mo   ON  mo.period_id   = wk.parent_period_id
JOIN   item_master im  ON  im.item        = sl.item
JOIN   deps        dp  ON  dp.dept        = im.dept
WHERE  mo.fiscal_year  = :v_fiscal_year
AND  mo.fiscal_month = :v_fiscal_month
AND  mo.period_type  = 'MO'
AND  mo.closed_ind   = 'Y'
GROUP BY dp.dept, dp.dept_name
ORDER BY dp.dept;

3. Last 13 fiscal weeks — rolling year comparison scaffold

-- Last 13 fiscal weeks with start/end dates (one quarter rolling)
-- Useful for building YoY sales comparison queries
SELECT wk.period_id,
wk.period_desc,
wk.start_date,
wk.end_date,
wk.fiscal_year,
wk.fiscal_month,
wk.fiscal_week,
RANK() OVER (ORDER BY wk.start_date DESC) AS weeks_ago
FROM   period        wk
JOIN   system_options so ON 1=1
WHERE  wk.period_type = 'WK'
AND  wk.end_date    [lt or eq to] so.vdate
ORDER BY wk.start_date DESC
FETCH FIRST 13 ROWS ONLY;

Common Gotchas for Developers#

• !

  Using Oracle SYSDATE instead of SYSTEM_OPTIONS.VDATE. In production they're the same. In UAT, staging, or when testing future scenarios, VDATE may be set weeks or months different from SYSDATE. Any custom PL/SQL that calls Oracle's SYSDATE directly will behave differently from standard RMS code and break date-sensitive testing.

• !

  Querying STKLEDGR for current stock instead of ITEM_LOC_SOH.

  The stock ledger holds period-close snapshots. The current open period's data in STKLEDGR is partial — it accumulates through the week but isn't finalized until period close. For current stock on hand, always use ITEM_LOC_SOH.

• !

  Not joining through PERIOD for fiscal date lookups. Using calendar month functions (TRUNC(date, 'MM')) in RMS reporting produces wrong groupings because fiscal months don't align with calendar months. Always join to PERIOD for any fiscal time aggregation.

• !

  Assuming PERIOD rows always exist for future dates. The PERIOD table is pre-populated for a fixed number of future years. If your RMS instance is old and PERIOD rows haven't been extended, future-dated POs, promotions, or replenishment dates will fail validation. Check the maximum END_DATE in PERIOD periodically and extend when needed.

• !

  Backdating transactions into a closed period. Once a period is closed (CLOSED_IND = 'Y'), its stock ledger snapshot is final. You cannot simply insert an inventory adjustment with a date in that closed period and expect the ledger to update — it won't. Any correction to a closed period requires a controlled re-open followed by re-posting, which is a DBA-supervised operation.

Key Takeaways#