AcumaticaSQLQueriesAndFunctions/ProjectDataAccuracy.sql

DROP VIEW IF EXISTS ProjectDataAccuracy
GO
Create View ProjectDataAccuracy
as
with FirstLayer  
as 
(
SELECT  c.CompanyID, 
		c.ContractID, 
                c.ContractCD,
               dbo.ProjectRental(c.CompanyID, c.ContractID) as RentalProject,
		ISNULL(CAST(dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeNTE', 1) AS DECIMAL(18, 2)), 0) as NTE,
		ISNULL(CAST(dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeVNTE', 1) AS DECIMAL(18, 2)), 0) as VNTE,
		dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeNTE', 0 ) as NTEAsText,
		dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeVNTE', 0 ) as VNTEAsText,
		ISNULL(b.RevisedExpenseBudgetSum,0) as RevisedExpenseBudgetSum,
		ISNULL(b.RevisedIncomeBudgetSum,0) as RevisedIncomeBudgetSum,
		ISNULL(b.ActualExpenseAmount,0) as ActualExpenseAmount,
		case when b.RevisedExpenseBudgetSum < 5 then 1 else 0 end as RevisedUnderFive,
		case when b.ActualExpenseAmount > b.RevisedExpenseBudgetSum then 1 else 0 end as ActualExpenseOverBudget,
                case when b.ActualExpenseAmount > b.RevisedIncomeBudgetSum then 1 else 0 end as ExpenseExceedsIncome,
CASE 
    WHEN b.RevisedIncomeBudgetSum + ISNULL(CAST(dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeNTE', 1) AS DECIMAL(18, 2)), 0) < 5 
    THEN 1 
    ELSE 0 
END AS BudgetIncomeAndNTEUnderFive,
CASE 
    WHEN ISNULL(CAST(dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeNTE', 1) AS DECIMAL(18, 2)), 0) < 5 
         AND b.RevisedIncomeBudgetSum > 0  
    THEN 1  
    ELSE 0 
END AS NTEUnderFiveWithBudget,
CASE 
    WHEN b.RevisedIncomeBudgetSum < 5 
         AND ISNULL(CAST(dbo.GetContractUserDefinedField(c.CompanyID, c.NoteID, 'AttributeNTE', 1) AS DECIMAL(18, 2)), 0) > 0 
    THEN 1  
    ELSE 0 
END AS BudgetUnderFiveWithNTE,
		ISNULL(b.RevisedIncomeBudgetSum,0) - ISNULL(RevisedExpenseBudgetSum,0) as EstimatedMargin,
		((ISNULL(b.RevisedIncomeBudgetSum, 0) - ISNULL(b.RevisedExpenseBudgetSum, 0)) / NULLIF(ISNULL(b.RevisedIncomeBudgetSum, 0), 0)) * 100 AS GrossMargin
from Contract c 
left join PMBudgetTotals b on c.CompanyID = b.CompanyID and c.ContractID = b.ProjectID
)
SELECT fl.*, 
       case when fl.GrossMargin < 0 then 1 else 0 end as BudgetedGMUnderZero,
       case when fl.GrossMargin < 8 then 1 else 0 end as BudgetedGMunderEight,
       case when fl.GrossMargin < 4.5 then 1 else 0 end as BudgetedGMUnderFourFive,
       case when fl.RentalProject = 1 and fl.GrossMargin < 4.5 then 1
       when fl.RentalProject = 0 and fl.GrossMargin < 8.0 then 1
       else 0 end as BudgetedGMUnderTargetCombined,
       case when fl.RevisedIncomeBudgetSum > IsNull(fl.NTE,0) then 1 else 0 end as BudgetedIncomeOverNTE
from FirstLayer fl