¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 1030074 |
| Summary | Is the budget for this 0-100 work spread across more than a one period? |
| Error message | 0-100 work found with BCWSi > 0 (Dollar, Hours, or FTEs) in more than one period. |
¶ 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 "0-100 Budget Spread Improperly" is designed to ensure that the budget for 0-100 work is not spread across more than one period. This check is performed on the DS03 Cost table.
The error message "0-100 work found with BCWSi > 0 (Dollar, Hours, or FTEs) in more than one period" indicates that the budget for 0-100 work has been improperly spread across multiple periods. This could be due to an error in data entry or a misunderstanding of how to allocate the budget for 0-100 work.
The fields that are likely causing this issue are BCWSi_dollars, BCWSi_hours, and BCWSi_FTEs. These fields represent the budgeted cost of work scheduled in dollars, hours, and full-time equivalents respectively. If any of these fields have a value greater than zero in more than one period for the same work package, it will trigger this error.
The expected values for these fields would be greater than zero in only one period for each work package. If the work is scheduled to be completed in one period, the budget for that work should also be allocated to that same period. If the budget is spread across multiple periods, it suggests that the work is also spread across those periods, which contradicts the definition of 0-100 work.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to check if the budget for 0-100 work is spread across more than one period. The 0-100 rule is a project management technique where the budget is allocated 0% at the start of the task and 100% upon completion. If the budget is spread across more than one period, it could indicate an error in budget allocation or tracking.
The severity of this test is marked as a 'MAJOR', which means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis. This could potentially lead to inaccurate budgeting or financial reporting, which could impact the overall management and success of the construction project.
Therefore, this check is important as it helps ensure the accuracy and integrity of the budget data, which is crucial for effective project management and financial control. It also helps identify potential issues early on, allowing for timely corrective actions.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_Is0To100BCWSInMoreThanAPeriod] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with SSpread as (
SELECT
WBS_ID_WP,
period_date [Period],
LEAD(period_date,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER BY period_date) AS NextPeriod,
BCWSi_dollars s_dollars,
BCWSi_hours s_hours,
BCWSi_FTEs s_ftes,
ISNULL(LEAD(BCWSi_dollars,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS NextS_dollars,
ISNULL(LEAD(BCWSi_hours,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS NextS_hours,
ISNULL(LEAD(BCWSi_ftes,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS NextS_ftes,
EOC,
ISNULL(is_indirect,'') IsInd
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EVT = 'F'
), Flags as (
SELECT WBS_ID_WP, [period], NextPeriod, EOC, IsInd
FROM SSpread
WHERE
(s_dollars > 0 AND NextS_dollars > 0) OR
(s_hours > 0 AND NextS_hours > 0) OR
(s_ftes > 0 AND NextS_ftes > 0)
GROUP BY WBS_ID_WP, [Period], NextPeriod, EOC, IsInd
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Flags F ON C.WBS_ID_WP = F.WBS_ID_WP
AND C.EOC = F.EOC
AND ISNULL(C.is_indirect,'') = F.IsInd
AND (C.period_date = F.[Period] OR C.period_date = F.NextPeriod)
WHERE
upload_ID = @upload_ID
AND C.EVT = 'F'
AND TRIM(ISNULL(C.WBS_ID_WP,'')) <> ''
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of is_indirect field. All cases where is_indirect = 'Y' are treated as indirect data. |