AcumaticaSQLQueriesAndFunctions/ProjectDataAccuracyUpdateTwo.sql

DROP VIEW IF EXISTS ProjectDataAccuracyUpdate
GO
Create View ProjectDataAccuracyUpdate
as
with FirstLayer  
as 
(
SELECT  c.CompanyID, 
		c.ContractID, 
        c.ContractCD,
		br.BranchID as BranchKey,
		br.BranchCD as BranchCD,
		ba.AcctName as BranchName,
		cust.BAccountID as CustomerID,
		cust.AcctCD as CustomerCD,
		cust.AcctName as CustomerName,
        c.CreatedDateTime as ContractCreatedDateTime,
        bAExec.AcctCD as ExecutiveNumber,
        bAExec.AcctName as ExecutiveName,
        bAProd.AcctCD as ProducerNumber,
        bAProd.AcctName as ProducerName,
        bAEst.AcctCD as EstimatorNumber,
        bAEst.AcctName as EstimatorName,
        bAAdmin.AcctCD as AdminNumber,
        bAAdmin.AcctName as AdminName,
        manager.AcctCD as ManagerNumber,
        manager.AcctName as ManagerName,
		manEmp.userID as managerPKID,
		execEmp.userID as ExecutivePKID,
		(SELECT sum(t.amount) from pmtran t where t.CompanyID = c.CompanyID and t.ProjectID = c.ContractID and t.AccountGroupID = 26 and t.Released = 1) as WIPActual,
     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,
		dbo.GetTopContractUserDefinedFieldDescription(c.CompanyID, c.NoteID, 'AttributeCategory') as JobCategory,
		dbo.GetTopContractUserDefinedFieldDescription(c.CompanyID, c.NoteID, 'AttributejobStatus') as JobStatus,
		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 BAccount cust on cust.CompanyID = c.CompanyID and cust.BAccountID = c.CustomerID
left join BAccount manager on manager.CompanyID = c.CompanyID and manager.DefContactID = c.OwnerID
left join EPEmployee manEmp on manager.CompanyID = manEmp.CompanyID and manEmp.BAccountID = manager.BAccountID
left JOIN ContractKvExt execNote on execNote.CompanyID = c.CompanyID and execNote.RecordID =c.NoteID and execNote.FieldName = 'AttributePROJECTEXE'
left join BAccount bAExec on BAExec.CompanyID = execNote.CompanyID and BAExec.AcctCD = execNote.ValueString
left join EPEmployee execEmp on BAExec.CompanyID = execEmp.CompanyID and BAExec.BAccountID = execEmp.BAccountID
left JOIN ContractKvExt prodNote on prodNote.CompanyID = c.CompanyID and prodNote.RecordID =c.NoteID and prodNote.FieldName = 'AttributePRODUCER'
left join BAccount bAProd on BAProd.CompanyID = prodNote.CompanyID and BAProd.AcctCD = prodNote.ValueString
left JOIN ContractKvExt estNote on estNote.CompanyID = c.CompanyID and estNote.RecordID =c.NoteID and estNote.FieldName = 'AttributeESTIMATOR'
left join BAccount bAEst on bAEst.CompanyID = estNote.CompanyID and BAEst.AcctCD = estNote.ValueString
left JOIN ContractKvExt adminNote on adminNote.CompanyID = c.CompanyID and adminNote.RecordID =c.NoteID and adminNote.FieldName = 'AttributePADMIN'
left join BAccount bAAdmin on BAAdmin.CompanyID = adminNote.CompanyID and BAAdmin.AcctCD = adminNote.ValueString
left join PMBudgetTotals b on c.CompanyID = b.CompanyID and c.ContractID = b.ProjectID
LEFT JOIN Branch br on br.CompanyID = c.CompanyID and br.BranchID = c.DefaultBranchID
LEFT join BACCount ba on ba.CompanyID = br.CompanyID and ba.BAccountID = br.BACCountID
), SecondLayer
as 
(
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
)
select sl.*, case when sl.BudgetedIncomeOverNTE + sl.BudgetedGMUnderTargetCombined + sl.BudgetUnderFiveWithNTE + sl.NTEUnderFiveWithBudget + sl.BudgetIncomeAndNTEUnderFive + ExpenseExceedsIncome + ActualExpenseOverBudget + RevisedUnderFive > 0 then 1 else 0 end as ProjectProblemExists
from SecondLayer sl