What Are Foundation Batches?

Foundation batches are the very first programs to execute in the nightly batch window. They establish the date context, load reference data, and set up the environment for all subsequent batch programs.

If foundation batches fail, every downstream batch must be held. No pricing, no replenishment, no inventory processing, no data extraction.

VDATE_JOB: Advancing the Business Date

The most critical foundation batch is VDATE_JOB. It advances the RMS business date (commonly called "VDATE") stored in the PERIOD table or SYSTEM_OPTIONS.

Why Not Just Use SYSDATE?

Every batch program in RMS uses VDATE, not SYSDATE, as the business date. This is because:

1. Time Zone Independence: RMS may run in a data center in US-East, but the business operates across US-West, Europe, and Asia. VDATE represents the "business day" regardless of server clock time.
2. Batch Sequence Control: VDATE advances only when the batch explicitly runs VDATE_JOB. If the batch window overruns into the next calendar day (past midnight), all programs still process using yesterday's VDATE — they don't accidentally jump to tomorrow's business date.
3. Recovery: If a critical batch fails and the team needs to reprocess yesterday's data, they can set VDATE back to yesterday and rerun the batch. SYSDATE cannot be rolled back.

What VDATE_JOB Does

-- Simplified VDATE_JOB logic
UPDATE system_options
SET    vdate = vdate + 1;

-- Also updates the current fiscal period tracking
UPDATE period
SET    period_status = 'C'  -- Close current period
WHERE  period_end_date = (SELECT vdate - 1 FROM system_options);

UPDATE period
SET    period_status = 'O'  -- Open new period
WHERE  period_start_date = (SELECT vdate FROM system_options);

COMMIT;

VDATE vs. SYSDATE

CURRRATE_LOAD: Exchange Rate Processing

For retailers operating in multiple currencies, CURRRATE_LOAD loads daily exchange rates from an external source (typically the corporate treasury system or a market data provider).

The Process

1. Exchange rate file arrives (CSV or flat file) from the treasury system
2. CURRRATE_LOAD reads the file and validates the rates
3. Rates are loaded into the CURRENCY_RATES table
4. All subsequent batch programs (PO costing, inventory valuation, financial reporting) use these rates for currency conversion

Rate Types

SELECT 
    currency_code,
    effective_date,
    exchange_rate,
    exchange_type,
    ROUND(1 / exchange_rate, 4) AS inverse_rate
FROM 
    currency_rates
WHERE 
    effective_date = (SELECT vdate FROM system_options)
ORDER BY 
    currency_code;

Fiscal Calendar Batches

The fiscal calendar batch updates period statuses as the business progresses through the year:

• Period Close: When the last day of a fiscal period passes, the period status changes from 'O' (Open) to 'C' (Closed)
• Half Close: When the last day of a fiscal half passes, the half is closed
• Year Close: At fiscal year-end, the year is closed and a new year begins

These status changes affect which periods can receive postings to the stock ledger and when financial reports can be finalized.

The Dependency Chain

Foundation batches are the root of the entire batch dependency tree:

VDATE_JOB ─────────────────────────────────────────┐
CURRRATE_LOAD ─────────────────────────────────────┤
FISCAL_DATE_UPDATE ────────────────────────────────┤
                                                    │
                    ┌───────────────────────────────▼
                    │     ALL OTHER BATCHES
                    │     
                    ├── Pricing Batches (need VDATE for effective date)
                    ├── Sales Posting (needs VDATE for business date)
                    ├── Inventory Batches (need VDATE + exchange rates)
                    ├── Replenishment (needs VDATE for lead time calc)
                    ├── Financial Batches (need fiscal period status)
                    └── Data Extraction (needs VDATE for delta window)

Foundation Batch Failure Scenarios

Best Practices