¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100455 |
| Summary | Do the DB transaction hours for this Control Account sum to something other than the DB in cost? |
| Error message | Sum of hours_delta where category = DB <> Sum of DS03.BCWSi_hours (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 Hour Transactions Misaligned with Cost (CA)" is designed to ensure that the sum of DB transaction hours for a given Control Account in the DS10 CC Log Detail table aligns with the DB in cost from the DS03 Cost table.
The test is performed by comparing the sum of 'hours_delta' in the DS10 CC Log Detail table where the category is 'DB' with the sum of 'BCWSi_hours' in the DS03 Cost table. Both sums are grouped by the 'WBS_ID_CA' field.
If the sums do not match, it indicates a discrepancy between the recorded hours and the cost, which could be due to data entry errors, incorrect calculations, or inconsistencies in the data.
The fields causing the issue are 'hours_delta' in the DS10 CC Log Detail table and 'BCWSi_hours' in the DS03 Cost table. The expected values for these fields should be equal when summed by 'WBS_ID_CA'.
If the test fails, the records in the DS10 CC Log Detail table with the category 'DB' and the 'WBS_ID' that matches the flagged 'WBS_ID_CA' will be returned for further investigation.
¶ Why do we check this?
This test is being performed to ensure that the Direct Billing (DB) transaction hours for a specific Control Account align with the DB in cost. In other words, the total number of hours billed directly should match the total cost billed directly for the same Control Account.
The importance of this check is to maintain the accuracy and consistency of the financial data. If the hours and cost do not align, it could indicate errors in billing, data entry, or cost allocation, which could lead to financial discrepancies and inaccuracies in project cost management.
The severity of this test is marked as a MAJOR. This means that while it may not immediately prevent the data from being reviewed, any discrepancies found could potentially cause problems during the analysis of the data. Therefore, it is recommended to address and resolve any issues found during this test to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreDBHoursMisalignedWithDS03CA] (
@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_hours) 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(hours_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
)
)