| Key | Value |
|---|---|
| Table | DS00 Metadata |
| Severity | MAJOR |
| Unique ID | 1000002 |
| Summary | Is the CPP Status Date after CD-4? |
| Error message | DS00.CPP_Status_Date > min DS04.ES_date or EF_date where milestone_level = 190. |
This DIQ check identifies when the CPP Status Date (data export date) occurs after the CD-4 milestone date. CD-4 represents project completion/approval to start operations.
The check triggers when:
This can occur for two reasons:
While post-CD-4 exports are allowed for project closeout, this check helps identify potential milestone data issues since the vast majority of uploads should occur before CD-4.
This test monitors whether data exports are occurring after the CD-4 (project completion) milestone. This check is valuable because:
As a MAJOR severity check, this does not block data uploads because legitimate project closeout activities sometimes require continued data exports after CD-4. However, given that post-CD-4 uploads are rare (<1% of cases), this check more commonly identifies milestone data quality issues that should be investigated.
CREATE FUNCTION [dbo].[fnDIQ_DS00_Meta_IsCPPSDGtCD4] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CD4 as (
SELECT cpp_status_date, COALESCE(MIN(ES_date), MIN(EF_date)) CD4Date
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level = 190
GROUP BY CPP_status_date
)
SELECT
*
FROM
DummyRow_Get(@upload_ID)
WHERE (SELECT COUNT(*) FROM CD4 WHERE cpp_status_date > CD4Date) > 0
)