AcumaticaSQLQueriesAndFunctions/ProjectInformationExtraColumns.sql

DROP VIEW IF EXISTS ProjectInformationExtraColumns
GO 
Create View ProjectInformationExtraColumns
as
with cte
as
(
SELECT b.CompanyID, b.ProjectID, sum(CASE when ag.GroupCD = 'UNBILLREV' then B.ActualAmount else 0 end) as 'UNBILLREV',sum(case when b.[type] = 'I' then b.ActualAmount else 0 end) as ActualIncomeSum
from Contract c 
join PMBudget b on c.CompanyID = b.CompanyID and c.ContractID = b.ProjectID 
join PMAccountGroup ag on ag.CompanyID = b.CompanyID and ag.GroupID = b.AccountGroupID 
group by b.CompanyID, b.ProjectID
), UA as 
(
SELECT t.CompanyID, t.ProjectID, sum(case when r.Module = 'AR' then t.amount * -1 else t.amount end * -1) as UnrecrevActual, sum(case when t.Released =1 then t.Amount else 0 end) as ReleasedAmount
from PMTran t
join PMRegister r on t.CompanyID = r.CompanyID and t.RefNbr = r.RefNbr 
group by t.CompanyID, t.ProjectID 
)
SELECT  c.CompanyID, 
		c.ProjectID,  
		c.UNBILLREV,
		c.ActualIncomeSum,
		u.UnrecrevActual,
		u.ReleasedAmount,
		dbo.GetProjectOpenInvoices(c.CompanyID, c.ProjectID) as wattersonUnpaidInvoicesAmount
FROM cte c
join UA u on c.CompanyID = u.CompanyID and c.ProjectID = u.projectID