¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 9040279 |
| Summary | Is this Work or Planning Planning with reprogramming missing in the CC Log detail? |
| Error message | WBS_ID where RPG = Y not found in DS10.WBS_ID list (compare by DS01.parent_WBS_ID where parent is of type CA or SLPP). |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "Reprogramming Missing in CC Log Detail (CA)" is designed to ensure that all Work or Planning Packages (WBS_ID) in the DS04 Schedule table that are marked for reprogramming (RPG = Y) are also present in the DS10 CC Log Detail table.
The error message "WBS_ID where RPG = Y not found in DS10.WBS_ID list" indicates that there are Work or Planning Packages in the DS04 Schedule table marked for reprogramming that are not found in the DS10 CC Log Detail table. This discrepancy could be due to missing entries in the DS10 CC Log Detail table or incorrect entries in the DS04 Schedule table.
The DIQ check groups the results by WBS_ID, which is the unique identifier for each Work or Planning Package. This allows you to easily identify which Work or Planning Packages are causing the issue.
The expected values for the fields causing the issue are as follows:
- In the DS04 Schedule table, the RPG field should be marked as 'Y' for all Work or Planning Packages that are being reprogrammed.
- In the DS10 CC Log Detail table, the WBS_ID field should contain all the Work or Planning Packages from the DS04 Schedule table that are marked for reprogramming.
If these conditions are not met, the DIQ check will fail and the error message will be displayed. To resolve this issue, ensure that all reprogrammed Work or Planning Packages in the DS04 Schedule table are also present in the DS10 CC Log Detail table.
¶ Why do we check this?
This test is being performed on the 'DS04 Schedule' table to check for the presence of reprogramming in the CC Log detail for work or planning planning. The test is checking if there are any WBS_IDs where reprogramming (RPG) is marked as 'Y' (Yes) but are not found in the DS10.WBS_ID list. This comparison is done by checking the DS01.parent_WBS_ID where the parent is of type CA or SLPP.
The importance of this check is to ensure that all reprogramming activities are properly logged and associated with the correct WBS_ID. This is crucial for maintaining accurate and complete project management data. If reprogramming activities are not properly logged, it could lead to inaccuracies in project scheduling, budgeting, and overall management.
The severity of this check is marked as 'MINOR', which means it is less severe but still indicates potential minor problems or deviations from best practices. It is not a critical error that would prevent the data from being reviewed, but it is a potential issue that should be addressed to ensure the highest quality and integrity of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsRPGWBSMissingInDS10CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CCLog as (
--CC log WBS IDs
SELECT WBS_ID
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID
), CARPG as (
--CA & WP WBSs with RPG
SELECT A.Ancestor_WBS_ID CAWBS, S.WBS_ID WPWBS
FROM DS04_schedule S INNER JOIN AncestryTree_Get(@upload_ID) A ON S.WBS_ID = A.WBS_ID
WHERE S.upload_ID = @upload_ID AND A.[Type] IN ('PP','SLPP') AND A.Ancestor_Type IN ('CA','SLPP') AND RPG = 'Y'
GROUP BY A.Ancestor_WBS_ID, S.WBS_ID
), Flags as (
--Composite, joined by CA WBS ID.
--Missed joins are CAs without CC log entries.
--Output is by WP WBS ID to join back to DS04.
SELECT WPWBS
FROM CARPG RPG LEFT OUTER JOIN CCLog C ON RPG.CAWBS = C.WBS_ID
WHERE C.WBS_ID IS NULL
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN Flags F ON S.WBS_ID = F.WPWBS
WHERE
S.upload_ID = @upload_ID
AND (--return only if there are WADs at the CA level.
SELECT COUNT(*)
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID
AND WBS_ID IN (
SELECT WBS_ID
FROM DS01_WBS
WHERE upload_ID = @upload_ID AND type IN ('CA','SLPP')
)
) > 0
)