¶ Basic Information
| Key | Value |
|---|---|
| Table | DS21 Rates |
| Severity | MINOR |
| Unique ID | 9210603 |
| Summary | Did the rate change for this labor resource between last FY and this FY exceed the allowed rate as provided in the IPMR header? |
| Error message |
¶ 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 "Labor Rate Escalation Exceeds Allowable Rate in the IPMR Header" is designed to verify if the rate change for a labor resource between the last fiscal year (FY) and the current FY exceeds the allowable rate as provided in the IPMR header. This check is performed on the DS21 Rates table.
The error is likely to be caused by a discrepancy between the rate change for a labor resource and the allowable rate. The rate change is calculated by subtracting the rate for the previous FY from the rate for the current FY, dividing the result by the rate for the previous FY, and taking the absolute value. This value is then compared to the escalation rate percentage in the DS07 table. If the rate change exceeds the escalation rate percentage by more than 0.02, the check will fail.
The fields causing the issue are the 'rate_dollars' field in the DS21 Rates table and the 'escalation_rate_pct' field in the DS07 table. The 'rate_dollars' field should contain the rate for a labor resource for a given FY, while the 'escalation_rate_pct' field should contain the allowable rate change as a percentage.
The expected values for these fields would be any valid rate for the 'rate_dollars' field and a percentage value for the 'escalation_rate_pct' field. The rate change calculated from the 'rate_dollars' field should not exceed the 'escalation_rate_pct' by more than 0.02.
¶ Why do we check this?
This test is being performed to ensure that the rate change for a labor resource from the last fiscal year (FY) to the current FY does not exceed the allowable rate as provided in the Integrated Program Management Report (IPMR) header. The test checks if the absolute difference between the current and previous FY's labor rate, divided by the previous FY's labor rate, exceeds the escalation rate percentage by more than 0.02.
This check is important because it helps maintain the integrity of the labor cost data in the DS21 Rates table. It ensures that the labor rate escalation is within acceptable limits, preventing potential overestimation or underestimation of labor costs. This is crucial for accurate project budgeting and cost management.
The severity of this check is marked as an MINOR, which means it's not a critical error but could indicate minor problems or deviations from best practices. If the labor rate escalation exceeds the allowable rate, it could potentially lead to inaccuracies in cost analysis and projections, but it won't necessarily prevent the data from being reviewed.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS21_Rates_IsLaborRateChangeGtDS07EscalationRate] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
With CurrSD AS (
SELECT cpp_status_date SD FROM DS07_IPMR_header WHERE upload_ID = @upload_ID
),
CurrFYRange AS (
SELECT
CASE
WHEN MONTH((SELECT SD FROM CurrSD)) >= 10 THEN CAST(CAST(YEAR((SELECT SD FROM CurrSD)) AS VARCHAR) + '/10/01' AS DATE)
ELSE CAST(CAST(YEAR((SELECT SD FROM CurrSD)) - 1 AS VARCHAR) + '/10/01' AS DATE) END AS FYStartDate,
CASE
WHEN MONTH((SELECT SD FROM CurrSD)) >= 10 THEN CAST(CAST(YEAR((SELECT SD FROM CurrSD)) + 1 AS VARCHAR) + '/09/30' AS DATE)
ELSE CAST(CAST(YEAR((SELECT SD FROM CurrSD)) AS VARCHAR) + '/09/30' AS DATE) END AS FYEndDate
),
PrevFYRates as (
SELECT resource_ID, rate_dollars
FROM DS21_rates R
WHERE
upload_ID = @upload_ID
AND EOC = 'Labor'
AND rate_start_date BETWEEN (SELECT DATEADD(m,-12,FYStartDate) FROM CurrFYRange)
AND (SELECT DATEADD(m,-12,FYEndDate) FROM CurrFYRange)
),
RateChange as (
SELECT Curr.resource_ID, ABS(((Curr.rate_dollars - Prev.rate_dollars) / NULLIF(Prev.rate_dollars,0))) RateChangePct
FROM DS21_rates Curr INNER JOIN PrevFYRates Prev ON Curr.resource_ID = Prev.resource_ID
WHERE
upload_ID = @upload_ID
AND Curr.EOC = 'LABOR'
AND Curr.rate_start_date BETWEEN (SELECT FYStartDate FROM CurrFYRange)
AND (SELECT FYEndDate FROM CurrFYRange)
)
SELECT
R.*
FROM
DS21_rates R INNER JOIN RateChange RC ON R.resource_ID = RC.resource_ID
WHERE
upload_ID = @upload_ID
AND RC.RateChangePct - (SELECT ISNULL(escalation_rate_pct,0) FROM DS07_IPMR_header WHERE upload_ID = @upload_ID) > .02
AND rate_start_date BETWEEN (SELECT FYStartDate FROM CurrFYRange)
AND (SELECT FYEndDate FROM CurrFYRange)
)