Oracle Retail Knowledge Base

Calendar validations (MFCS-AIF)

2 min read

Context

Read-only checks that validate the calendar load into AIF DATA when the calendar is sourced through MFCS integration (not a file upload). The integration loads the staging table W_MCAL_PERIOD_DS directly, so the file-transfer staging (_FTS) tables stay empty and are not checked here. Run these in APEX: first against W_MCAL_PERIOD_DS to confirm the data landed cleanly, then against the calendar dimension tables once the load procedures have completed. None of these queries modify data.

Quick access

The calendar ID is environment-specific

The MCAL_CAL_ID value below (Retail Calendar~41) is specific to the loaded calendar. Adjust it to match the calendar in your environment before running the check.

Staging checks

Once MFCS integration has loaded the staging table W_MCAL_PERIOD_DS, run these to confirm the data is clean before it is staged further.

Row counts match the data file

-- Verify initial calendar data before staging it further; row counts should match the data file.
SELECT * FROM W_MCAL_PERIOD_DS;

Column counts (null check)

-- All counts should be the same. This indirectly checks for nulls in required columns.
SELECT 
	count(*),
	count(MCAL_CAL_ID),
	count(MCAL_PERIOD_TYPE),
	count(MCAL_PERIOD_NAME),
    count(MCAL_PERIOD),
    count(MCAL_PERIOD_ST_DT),
    count(MCAL_PERIOD_END_DT),
    count(MCAL_QTR),
    count(MCAL_YEAR),
    count(MCAL_QTR_START_DT),
    count(MCAL_QTR_END_DT),
    count(MCAL_YEAR_START_DT),
    count(MCAL_YEAR_END_DT)
FROM W_MCAL_PERIOD_DS;

No missing or wrong calendar ID

-- Should not return any rows.
SELECT * FROM W_MCAL_PERIOD_DS
WHERE 
	MCAL_CAL_ID IS NULL
	OR MCAL_CAL_ID != 'Retail Calendar~41';

No duplicate periods

-- Checking for duplicate rows. Should not return any rows.
SELECT 
	MCAL_YEAR,
	MCAL_PERIOD_NAME,
	count(*)
FROM W_MCAL_PERIOD_DS
GROUP BY MCAL_YEAR, MCAL_PERIOD_NAME
HAVING count(MCAL_PERIOD_NAME) > 1;

12 periods per year

-- Number of periods per year should always be 12.
SELECT 
	MCAL_YEAR,
	count(MCAL_PERIOD_NAME)
FROM W_MCAL_PERIOD_DS
GROUP BY MCAL_YEAR
ORDER BY MCAL_YEAR;
SELECT 
	MCAL_YEAR,
	count(MCAL_PERIOD)
FROM W_MCAL_PERIOD_DS
GROUP BY MCAL_YEAR
ORDER BY MCAL_YEAR;

Post-load checks

After the load procedures complete, confirm the calendar dimension tables are populated.

-- After load procedures completed, check the following tables.
select /*+ OPT_PARAM('_optimizer_answering_query_using_stats' 'FALSE') */ count(*) from W_MCAL_PERIOD_D;
 
select /*+ OPT_PARAM('_optimizer_answering_query_using_stats' 'FALSE') */ count(*) from W_MCAL_DAY_D;
 
select /*+ OPT_PARAM('_optimizer_answering_query_using_stats' 'FALSE') */ count(*) from W_MCAL_WEEK_D;

Reference

Graph View

Backlinks