Demand planning API rejects forecast uploads with 'Invalid date format' error despite ISO 8601 compliance

SCM Epicor SCM
, , , , , , , ,
1

Our forecast automation is failing when uploading forecast data to the demand planning module via REST API. We’re getting “Invalid date format” errors even though we’re using ISO 8601 format.

The REST API payload requirements we’re sending:

POST /api/v1/forecasts/upload
{
  "forecastPeriod": "2024-12",
  "items": [
    {"sku": "PROD-001", "quantity": 1000, "date": "2024-12-01T00:00:00Z"}
  ]
}

Date/time format validation passes in our tests, but Epicor rejects it. When we try different formats like “2024-12-01” or “12/01/2024”, we get the same error. Our forecast data integration pulls from Excel files where dates are in various formats. Running ES 10.2.500. What’s the exact date format Epicor expects for forecast uploads?

2

In ES 10.2.500, the demand planning API expects dates in a specific format that’s not well documented. Try using “YYYY-MM-DD” without time component for forecast dates. The forecastPeriod field should be “YYYY-MM” format. Also, make sure you’re not mixing date formats within the same payload - all dates must use the same format consistently.

3

That makes sense now. So for monthly forecasts, forecastPeriod is “YYYY-MM” and item dates are “YYYY-MM-01”. I’ll update our Excel import logic to always set the day to 01. One more question - does the time component matter if we include it, or should we omit it entirely?

4

The issue is that forecastPeriod and date fields have different format requirements. The forecastPeriod should be “YYYY-MM” (month level), but the item date field needs to match the granularity configured in your demand planning settings. If you’re using weekly forecasts, use “YYYY-Www” format (ISO week). For daily forecasts, use “YYYY-MM-DD”. Check your demand planning configuration to see what granularity is set - it’s under Demand Planning > Configuration > Forecast Settings.

5

Thanks Johnson. I tried “YYYY-MM-DD” format but still getting the error. Could it be related to timezone? Our Excel files don’t have timezone information, but when we convert to JSON we’re adding the “Z” suffix for UTC. Maybe Epicor wants a specific timezone offset instead?

6

I’ll provide a complete solution addressing all three focus areas:

Date/Time Format Validation:

Epicor demand planning API has strict date format requirements that vary based on forecast granularity:

  1. Monthly Forecasts:

    • forecastPeriod: “YYYY-MM” (e.g., “2024-12”)
    • item date: “YYYY-MM-01” (must be first day of month, no time component)
    • Invalid: “2024-12-15”, “2024-12-01T00:00:00Z”, “12/01/2024”

  2. Weekly Forecasts:

    • forecastPeriod: “YYYY-Www” (e.g., “2024-W48” for week 48)
    • item date: “YYYY-MM-DD” (must be Monday of the week)
    • Example: “2024-11-25” (Monday of week 48)

  3. Daily Forecasts:

    • forecastPeriod: “YYYY-MM” (month containing the forecasts)
    • item date: “YYYY-MM-DD” (any valid day)
    • Time component is ignored if provided

Critical Rules:

  • Never include time components (T00:00:00) or timezone indicators (Z, +00:00)
  • All dates must align with the forecast calendar configured in Demand Planning > Configuration
  • Dates must fall within the planning horizon (typically 12-24 months from current date)
  • forecastPeriod must match the earliest item date’s period

REST API Payload Requirements:

Correct payload structure for monthly forecasts:

POST /api/v1/forecasts/upload
{
  "forecastPeriod": "2024-12",
  "forecastType": "STATISTICAL",
  "planningGroup": "FG-PRODUCTS",
  "items": [
    {
      "sku": "PROD-001",
      "quantity": 1000,
      "date": "2024-12-01",
      "uom": "EA"
    },
    {
      "sku": "PROD-002",
      "quantity": 1500,
      "date": "2025-01-01",
      "uom": "EA"
    }
  ]
}

Required fields:

  • forecastPeriod: Starting period for the forecast data
  • items: Array of forecast items (max 1000 per request)
  • items[].sku: Product SKU (must exist in product master)
  • items[].quantity: Forecast quantity (positive decimal)
  • items[].date: Forecast date (format per granularity rules)

Optional but recommended:

  • forecastType: “STATISTICAL”, “CONSENSUS”, or “OVERRIDE” (default: STATISTICAL)
  • planningGroup: Used for filtering/organizing forecasts
  • items[].uom: Unit of measure (default: base UOM from product master)
  • items[].confidence: Confidence level 0-100 (default: 80)

Forecast Data Integration Best Practices:

  1. Excel Date Handling: Excel stores dates as serial numbers. When converting to JSON:
# Python example
import pandas as pd
from datetime import datetime

# Read Excel
df = pd.read_excel('forecast.xlsx')

# Convert Excel dates to proper format
df['date'] = pd.to_datetime(df['date']).dt.strftime('%Y-%m-01')

# Ensure all dates are first of month for monthly forecasts
df['date'] = df['date'].apply(lambda x:
    datetime.strptime(x, '%Y-%m-%d').replace(day=1).strftime('%Y-%m-%d')
)
  1. Validation Before Upload: Implement pre-upload validation:
  • Verify date format matches configured granularity
  • Check all SKUs exist in Epicor product master
  • Validate quantities are positive
  • Ensure dates fall within planning horizon
  • Confirm forecastPeriod matches earliest item date
  1. Error Handling: API returns detailed validation errors:
{
  "error": "Invalid date format",
  "details": [
    {
      "field": "items[2].date",
      "value": "2024-12-15",
      "message": "Date must be first day of month for monthly forecasts"
    }
  ]
}

Parse the details array to identify which specific items failed validation.

  1. Batch Processing: For large forecast files:
  • Split into batches of 500-1000 items per API request
  • Process batches sequentially with 2-second delay between requests
  • Log failed items for manual review
  • Implement retry logic for transient errors (HTTP 5xx)
  1. Date Consistency: Maintain a date format configuration file:
{
  "granularity": "monthly",
  "forecastPeriodFormat": "YYYY-MM",
  "itemDateFormat": "YYYY-MM-01",
  "planningHorizonMonths": 18
}

Use this configuration across all forecast upload processes to ensure consistency.

  1. Testing Strategy: Before deploying forecast automation:
  • Test with single-item payloads to verify date format acceptance
  • Test boundary dates (first/last day of planning horizon)
  • Test mixed granularities if you use multiple forecast types
  • Validate against Epicor’s forecast calendar (holidays, fiscal periods)

After implementing these date format rules and validation practices, your forecast upload success rate should reach 98%+. The key is aligning your date formats with Epicor’s granularity configuration and always using the first day of the period for monthly forecasts.