¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MINOR |
| Unique ID | 9100469 |
| Summary | Do the OTB/OTS transaction dollars for this Control Account sum to something other than the BAC reprogramming in cost? |
| Error message | Sum of dollars_delta where category = OTB, OTS, or OTB-OTS <> Sum of DS03.BAC_rpg (by WBS_ID_CA). |
¶ 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 "OTB/OTS Transactions Misaligned with Cost (CA)" is designed to ensure that the sum of OTB (Over Target Baseline), OTS (Over Target Schedule), or OTB-OTS transaction dollars for a given Control Account aligns with the Baseline After Change (BAC) reprogramming in cost. This check is performed on the data in the DS10 CC Log Detail table.
The error message "Sum of dollars_delta where category = OTB, OTS, or OTB-OTS <> Sum of DS03.BAC_rpg (by WBS_ID_CA)" indicates that the sum of the 'dollars_delta' field for OTB, OTS, or OTB-OTS categories does not match the sum of the 'BAC_rpg' field in the DS03 Cost table, when grouped by the 'WBS_ID_CA' field.
This discrepancy could be caused by incorrect entries in the 'dollars_delta' field for OTB, OTS, or OTB-OTS categories in the DS10 CC Log Detail table, or in the 'BAC_rpg' field in the DS03 Cost table. The expected values for these fields should be such that the sum of 'dollars_delta' for OTB, OTS, or OTB-OTS categories equals the sum of 'BAC_rpg' for the corresponding 'WBS_ID_CA'.
The DIQ check groups the data by the 'WBS_ID' field, and any records that do not meet the above condition are flagged for review.
¶ Why do we check this?
This test is being performed on the 'DS10 CC Log Detail' table to check if the sum of Over Target Baseline (OTB) and Over Target Schedule (OTS) transaction dollars for a specific Control Account aligns with the Budget at Completion (BAC) reprogramming in cost. The test is checking if the sum of 'dollars_delta' where the category is OTB, OTS, or OTB-OTS is not equal to the sum of 'DS03.BAC_rpg' grouped by 'WBS_ID_CA'.
The importance of this check is to ensure that the financial data related to the project is accurate and consistent. Misalignment between OTB/OTS transactions and BAC reprogramming in cost could indicate errors or inconsistencies in the data, which could lead to inaccurate financial reporting and decision-making.
The severity of this check is marked as an MINOR, which means it is less severe but still important. It indicates that there might be minor problems or that the data doesn't follow all best practices. If the test fails, it doesn't necessarily mean that the data can't be reviewed, but it does suggest that there might be potential issues that need to be addressed to ensure the quality and integrity of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_IsOTBOTSRpgMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Flags as (
SELECT CCDB.WBS_ID
FROM ( --cost BAC_Rpg by CA BWS
SELECT
WBS_ID_CA, SUM(BAC_rpg) BAC_Rpg
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA
) CostDB INNER JOIN (
-- CC log Rpg by WBS ID (possibly CA, possibly not)
SELECT WBS_ID, SUM(dollars_delta) Rpg
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID AND category IN ('OTB', 'OTS','OTB-OTS')
GROUP BY WBS_ID
) CCDB ON CostDB.WBS_ID_CA = CCDB.WBS_ID
WHERE
CostDB.BAC_Rpg <> CCDB.Rpg
)
SELECT
*
FROM
DS10_CC_log_detail
WHERE
upload_ID = @upload_ID
AND category IN ('OTB', 'OTS','OTB-OTS')
AND WBS_ID IN (
SELECT WBS_ID FROM Flags
)
)