¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100453 |
| Summary | Do the DB transaction dollars for this Control Account 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_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 "DB Dollar Transactions Misaligned with Cost (CA)" is designed to ensure that the sum of transaction dollars in the DS10 CC Log Detail table aligns with the sum of BCWSi_dollars in the DS03 Cost table for each Control Account.
The test is performed by comparing the sum of 'dollars_delta' in the DS10 CC Log Detail table where the category is 'DB' with the sum of 'BCWSi_dollars' in the DS03 Cost table. These sums are grouped by the 'WBS_ID_CA' in the DS03 Cost table and the 'WBS_ID' in the DS10 CC Log Detail table.
If the sums do not match for a given Control Account, the DIQ check will flag an error. This discrepancy could be caused by incorrect entries in either the 'dollars_delta' field in the DS10 CC Log Detail table or the 'BCWSi_dollars' field in the DS03 Cost table.
To resolve this issue, ensure that the sum of 'dollars_delta' for each Control Account in the DS10 CC Log Detail table matches the sum of 'BCWSi_dollars' for the same Control Account in the DS03 Cost table.
¶ Why do we check this?
This test is being performed to ensure that the dollar transactions in the database (DB) for a specific Control Account align with the DB in cost. The test checks if the sum of 'dollars_delta' where the category equals DB is not equal to the sum of 'DS03.BCWSi_dollars' (by WBS_ID_CA).
The importance of this check is to maintain the integrity and accuracy of financial data within the EVMS construction project management data. Misalignment between the DB transaction dollars and the DB in cost could lead to inaccurate financial reporting, budgeting, and project cost management.
The severity of this check is marked as a MAJOR. This means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed. It is crucial to correct this issue to ensure accurate financial data analysis and reporting.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreDBDollarsMisalignedWithDS03CA] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Flags as (
SELECT CCDB.WBS_ID
FROM ( --cost DB by CA BWS
SELECT
WBS_ID_CA, SUM(BCWSi_dollars) DB
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_CA
) CostDB INNER JOIN (
-- CC log DB by WBS ID (possibly CA, 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_CA = 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
)
)