¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MINOR |
| Unique ID | 9100470 |
| Summary | Do the OTB/OTS transaction dollars for this Work Package 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_WP). |
¶ 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 (WP)" is designed to ensure that the sum of OTB (Over Target Baseline), OTS (Over Target Schedule), or OTB-OTS transaction dollars for a specific Work Package aligns with the Budget at Completion (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_WP)" indicates that the sum of the transaction dollars (dollars_delta) in the categories OTB, OTS, or OTB-OTS does not match the sum of the BAC reprogramming (BAC_rpg) in the DS03 cost table for the same Work Package (WBS_ID_WP).
This discrepancy could be caused by incorrect entries in the dollars_delta field in the DS10 CC Log Detail table or in the BAC_rpg field in the DS03 cost table. The expected values in these fields should be such that the sum of the transaction dollars for OTB, OTS, or OTB-OTS categories equals the sum of the BAC reprogramming for the same Work Package.
The DIQ check groups the results by the Work Breakdown Structure ID (WBS_ID), allowing you to identify which Work Packages have discrepancies between the transaction dollars and the BAC reprogramming.
¶ Why do we check this?
This test is being performed to ensure that the Over Target Baseline (OTB) and Over Target Schedule (OTS) transaction dollars for a specific Work Package align with the Budget at Completion (BAC) reprogramming in cost. The test checks if the sum of the changes in dollars (dollars_delta) where the category is OTB, OTS, or OTB-OTS is not equal to the sum of the BAC reprogramming (DS03.BAC_rpg) for each Work Breakdown Structure ID Work Package (WBS_ID_WP).
The importance of this check is to maintain the integrity of the financial data in the DS10 CC Log Detail table. If the OTB/OTS transactions are not aligned with the BAC reprogramming in cost, it could lead to inaccurate financial reporting and budgeting. This could potentially cause problems during the analysis of the project's cost performance and financial health.
The severity of this check is marked as an MINOR. This means that while it may not immediately prevent the data from being reviewed, it could indicate minor problems or suggest that the data does not adhere to all best practices. Therefore, it is recommended to address this issue to ensure the accuracy and reliability of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_IsOTBOTSRpgMisalignedWithDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Flags as (
SELECT CCDB.WBS_ID
FROM ( --cost BAC_Rpg by WP BWS
SELECT
WBS_ID_WP, SUM(BAC_rpg) BAC_Rpg
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP
) CostDB INNER JOIN (
-- CC log Rpg by WBS ID (possibly WP, 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_WP = 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
)
)