¶ Basic Information
| Key | Value |
|---|---|
| Table | DS09 CC Log |
| Severity | MAJOR |
| Unique ID | 9090446 |
| Summary | Is the PM name misaligned with what is in the WADs? |
| Error message | PM <> DS08.PM where approved_date > CPP_SD_Date - 1. |
¶ 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 "PM Misaligned with WADs" is designed to identify any discrepancies between the Project Manager (PM) names listed in the DS09 CC Log and those in the DS08 WADs.
This check is triggered when the approved date in the DS09 CC Log is later than the last period date before the CPP status date in the DS03 Cost table. It then compares the PM names in the DS09 CC Log with those in the DS08 WADs that have an authorization date later than the last period date.
If the PM name in the DS09 CC Log does not match any PM names in the DS08 WADs, an error is flagged. This discrepancy could be due to a data entry error, a change in project management not reflected in the WADs, or a delay in updating the WADs.
Please ensure that the PM names in the DS09 CC Log and DS08 WADs are consistent and that the WADs are updated promptly when there are changes in project management. The approved date in the DS09 CC Log should also be checked to ensure it is not later than the last period date before the CPP status date in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to ensure that the Project Manager (PM) name in the 'DS09 CC Log' table aligns with the PM name in the Work Authorization Documents (WADs). The test checks if there is a mismatch between the PM names in these two sources where the approval date is greater than the Contract Performance Period Start Date (CPP_SD_Date) minus one.
The importance of this check is to maintain consistency and accuracy in the project management data. If the PM names are misaligned, it could lead to confusion, miscommunication, or errors in project management and reporting. This could potentially impact the project's progress and outcomes.
The severity level of this test is 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is crucial to address this issue to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS09_CCLog_IsPMMisalignedWithDS08] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with LastPeriod as (
SELECT period_date
FROM (
SELECT period_date, ROW_NUMBER() OVER (ORDER BY period_date DESC) AS row_num
FROM DS03_cost
WHERE upload_ID = @upload_ID AND period_date < CPP_status_date
) subquery
WHERE row_num = 1
), WADs as (
SELECT *
FROM DS08_WAD
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS09_CC_log
WHERE
upload_ID = @upload_ID
AND approved_date > (SELECT TOP 1 period_date FROM LastPeriod)
AND PM NOT IN (
SELECT PM
FROM WADs
WHERE auth_PM_date > (SELECT TOP 1 period_date FROM LastPeriod)
)
AND (SELECT COUNT(*) FROM WADs) > 0 -- run only if there are WADs
)