| Key | Value |
|---|---|
| Table | DS22 Financial Calendar |
| Severity | MAJOR |
| Unique ID | 9220003 |
| Summary | Have the non-future DS22 period_dates changed from the prior upload? |
| Error message | DS22_financial_calendar.period_date list <> DS22_financial_calendar.period_date list for prior upload. |
This DIQ check compares the current upload's financial calendar (DS22) against the most recent previously published upload's DS22 for the same project, and flags when the established (non-future) period_dates have changed.
The prior upload is resolved by:
EVMNext_DIQ.dbo.DIQ_Runspublished = 1 upload for that PARSID in EVMNext.dbo.EVMNext_Status (ordered by CPP_status_date descending)Both calendars are then restricted to period_dates on or before the prior upload's CPP_status_date (dates after it are treated as future and ignored). The check fails when either:
This typically occurs due to:
Adding new future period_dates, or re-forecasting period_dates after the prior status date, does not trigger this check.
This test ensures the reported (actual) portion of the financial calendar stays stable across submissions. This validation is important because:
As a MAJOR check, a change to the established calendar should be reviewed before the data is relied upon, since shifting historical period boundaries invalidates trend analysis against prior submissions.
CREATE FUNCTION [dbo].[fnDIQ_DS22_FinCal_PeriodDatesChangedFromPrior] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
/* Flags when the non-future DS22 period_dates differ (in either direction) from the prior
published upload's DS22. "Future" is relative to the PRIOR upload's CPP_status_date;
period_dates after it are ignored on both sides, so re-forecasting or adding future
periods is permitted. Returns a dummy row on any mismatch. */
with Cur as (
-- PARSID of the current upload
SELECT TOP 1 PARSID
FROM DIQ_Runs
WHERE upload_id = @upload_id
ORDER BY run_time DESC
),
Prior as (
-- most recent published upload for the same project
SELECT TOP 1 s.upload_id, TRY_CAST(s.CPP_status_date AS date) AS status_date
FROM EVMNext.dbo.EVMNext_Status s
WHERE s.PARSID = (SELECT TRY_CAST(Cur.PARSID AS int) FROM Cur)
AND s.published = 1
ORDER BY TRY_CAST(s.CPP_status_date AS date) DESC, s.upload_id DESC
),
PriorDates as (
SELECT DISTINCT period_date
FROM DS22_financial_calendar
WHERE upload_id = (SELECT upload_id FROM Prior)
AND period_date <= (SELECT status_date FROM Prior)
),
CurrDates as (
SELECT DISTINCT period_date
FROM DS22_financial_calendar
WHERE upload_id = @upload_id
AND period_date <= (SELECT status_date FROM Prior)
)
SELECT *
FROM DummyRow_Get(@upload_id)
WHERE EXISTS (SELECT 1 FROM Prior) -- no prior published upload -> nothing to compare, pass
AND (
-- a prior non-future date is gone from the current calendar
EXISTS (SELECT 1 FROM PriorDates p WHERE p.period_date NOT IN (SELECT period_date FROM CurrDates))
OR
-- a current non-future date was not in the prior calendar
EXISTS (SELECT 1 FROM CurrDates c WHERE c.period_date NOT IN (SELECT period_date FROM PriorDates))
)
)