¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060297 |
| Summary | Are the resource remaining dollars flowing up to the schedule WBS misaligned with the BCWR in cost (by WBS_ID & EOC)? |
| Error message | Sum of resource remaining dollars rolled up into DS04.WBS_ID do not align with BCWR in DS03 (by WBS_ID & EOC). |
¶ 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 "Resource Remaining Dollars Misaligned with Cost BCWR" is designed to ensure that the sum of resource remaining dollars, when rolled up into the Work Breakdown Structure (WBS) in DS04, aligns with the Budgeted Cost of Work Remaining (BCWR) in DS03. This alignment is checked for each WBS ID and Estimate at Completion (EOC).
If an error is flagged by this DIQ check, it indicates that there is a discrepancy between the remaining resource dollars and the BCWR for a specific WBS ID and EOC. This could be due to incorrect data entry in either DS06 Resources or DS03 Cost.
In DS06 Resources, the fields that could be causing the issue are 'task_ID', 'EOC', and 'remaining_dollars'. The 'remaining_dollars' field should accurately reflect the remaining dollars for each task and EOC.
In DS03 Cost, the fields that could be causing the issue are 'WBS_ID_WP', 'EOC', 'BCWSi_dollars', and 'BCWPi_dollars'. The BCWR is calculated as the difference between 'BCWSi_dollars' and 'BCWPi_dollars' for each WBS ID and EOC.
To resolve the issue, review the data entries in these fields and ensure that they accurately reflect the remaining resource dollars and the BCWR for each WBS ID and EOC.
¶ Why do we check this?
This test is being performed to ensure that the remaining resource dollars, when rolled up to the schedule Work Breakdown Structure (WBS), align with the Budgeted Cost of Work Remaining (BCWR) in the cost data. The test is checking for any discrepancies between the sum of remaining resource dollars and the BCWR by WBS_ID and End of Contract (EOC).
The importance of this check lies in maintaining the accuracy and consistency of financial data within the project. Misalignment between these two figures could indicate errors in budgeting, resource allocation, or financial reporting, which could potentially lead to financial mismanagement or inaccurate project forecasting.
The severity of this check is marked as an MINOR, which means it's not critical but could cause minor problems or indicate that the data doesn't follow all best practices. It's a signal to review the data and correct any potential issues to ensure the integrity and quality of the project's financial data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_AreRemDollarsMisalignedWithDS03BCWR] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostWBS as (
SELECT WBS_ID_WP WBS, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END as EOC, SUM(BCWSi_dollars) - SUM(BCWPi_dollars) BCWR
FROM DS03_cost
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID_WP, CASE WHEN ISNULL(is_indirect,'') = 'Y' THEN 'Indirect' ELSE EOC END
), Resources as (
SELECT task_ID, EOC, ISNULL(subproject_ID,'') SubP, SUM(remaining_dollars) RemDollars
FROM DS06_schedule_resources
WHERE upload_ID = @upload_ID AND schedule_type = 'FC'
GROUP BY task_ID, EOC, ISNULL(subproject_ID,'')
), ScheduleWBS as (
SELECT S.WBS_ID WBS, R.EOC, SubP, SUM(R.RemDollars) RemDollars
FROM DS04_schedule S INNER JOIN Resources R ON S.task_ID = R.task_ID AND ISNULL(S.subproject_ID,'') = R.SubP
WHERE S.upload_ID = @upload_ID AND S.schedule_type = 'FC'
GROUP BY S.WBS_ID, R.EOC, SubP
), FlagsByWBS as (
SELECT S.WBS, S.EOC, S.SubP
FROM ScheduleWBS S INNER JOIN CostWBS C ON C.EOC = S.EOC
AND C.WBS = S.WBS
AND C.BCWR <> S.RemDollars
), FlagsByTask as (
SELECT S.task_ID, F.EOC, F.SubP
FROM DS04_schedule S INNER JOIN FlagsByWBS F ON S.WBS_ID = F.WBS AND ISNULL(S.subproject_ID,'') = F.SubP
WHERE upload_ID = @upload_ID AND schedule_type = 'FC'
)
SELECT
R.*
FROM
DS06_schedule_resources R INNER JOIN FlagsByTask F ON R.task_ID = F.task_ID
AND ISNULL(R.subproject_ID,'') = F.SubP
AND R.EOC = F.EOC
WHERE
R.upload_id = @upload_ID
AND R.schedule_type = 'FC'
AND F.EOC = R.EOC
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to use 'is_indirect' field as part of logic for determining indirect data, rather than simply using the 'EOC' field. 2. Logic adjusted to account for the addition of 'subproject_ID'. |