¶ Basic Information
| Key | Value |
|---|---|
| Table | DS10 CC Log Detail |
| Severity | MAJOR |
| Unique ID | 9100456 |
| Summary | Do the DB transaction hours for this Work Package 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_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 Hour Transactions Misaligned with Cost (WP)" is designed to ensure that the sum of transaction hours for a given Work Package in the DS10 CC Log Detail table aligns with the corresponding cost in 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' (Direct Billing), with the sum of 'BCWSi_hours' in the DS03 Cost table. Both sums are grouped by the Work Package ID ('WBS_ID_WP' in DS03 and 'WBS_ID' in DS10).
If the sums do not match, it indicates a discrepancy between the recorded hours and the associated cost for a Work Package. This could be due to errors in data entry, such as incorrect logging of hours or misclassification of transactions under the 'DB' category.
The expected values for 'hours_delta' in the DS10 CC Log Detail table and 'BCWSi_hours' in the DS03 Cost table should be equal for each Work Package when summed up. If they are not, the DIQ check will flag these records for review.
Please ensure that all hours and costs are accurately recorded and classified in the respective tables to maintain data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that the total hours recorded for a specific Work Package in the 'DS10 CC Log Detail' table align with the corresponding cost in the database. The test checks if the sum of 'hours_delta' where the category is 'DB' is not equal to the sum of 'DS03.BCWSi_hours' for the same Work Package (identified by 'WBS_ID_WP').
The importance of this check lies in maintaining the accuracy and consistency of data related to project management. If the hours and costs are misaligned, it could lead to incorrect analysis and decision-making based on this data. This could potentially impact the project's budgeting, scheduling, and overall management.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is crucial to address this issue to ensure the reliability of the data and the subsequent analysis.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS10_CCLogDetails_AreDBHoursMisalignedWithDS03WP] (
@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_hours) 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(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_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
)
)