¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100454 |
| Summary | Do the DB transaction dollars for this Work Package sum to something other than the DB in cost? |
| Error message | Sum of dollars_delta where category = DB <> Sum of DS03.BCWSi_dollars (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 "DB Dollar Transactions Misaligned with Cost (WP)" is designed to ensure that the sum of DB transaction dollars for a given Work Package aligns with the DB in cost. This check is performed on the DS10 CC Log Detail table.
The error message "Sum of dollars_delta where category = DB <> Sum of DS03.BCWSi_dollars (by WBS_ID_WP)" indicates that there is a discrepancy between the sum of DB transaction dollars and the DB in cost for a specific Work Package. This discrepancy is likely caused by an error in the data entry process, where the sum of dollars_delta in the DS10 CC Log Detail table does not match the sum of BCWSi_dollars in the DS03 Cost table.
The fields causing this issue are the dollars_delta field in the DS10 CC Log Detail table and the BCWSi_dollars field in the DS03 Cost table. The expected values for these fields should be equal for a given Work Package, as indicated by the WBS_ID_WP field. If these values are not equal, it suggests that there may be an error in the data that needs to be corrected.
¶ Why do we check this?
This test is being performed to ensure that the dollar transactions for a specific Work Package (WP) in the DS10 CC Log Detail table align with the cost in the database (DB). The test checks if the sum of the dollar transactions (dollar_delta) where the category is DB is not equal to the sum of the DS03.BCWSi_dollars (by WBS_ID_WP).
The importance of this check is to maintain the integrity and accuracy of the financial data related to the Work Packages. If the dollar transactions do not match the cost in the database, it could lead to inaccurate financial reporting and analysis, which could impact decision-making and project management.
The severity of this check is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. It is crucial to ensure that the dollar transactions align with the cost in the database to maintain the accuracy of the financial data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreDBDollarsMisalignedWithDS03WP] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Flags as (
SELECT CCDB.WBS_ID
FROM ( --cost DB by WP BWS
SELECT
WBS_ID_WP, SUM(BCWSi_dollars) DB
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP
) CostDB INNER JOIN (
-- CC log DB by WBS ID (possibly WP, possibly not)
SELECT WBS_ID, SUM(dollars_delta) DB
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID AND category = 'DB'
GROUP BY WBS_ID
) CCDB ON CostDB.WBS_ID_WP = CCDB.WBS_ID
WHERE
CostDB.DB <> CCDB.DB
)
SELECT
*
FROM
DS10_CC_log_detail
WHERE
upload_ID = @upload_ID
AND category = 'DB'
AND WBS_ID IN (
SELECT WBS_ID FROM Flags
)
)