AcumaticaSQLQueriesAndFunctions/ProjectInformation.sql

IF OBJECT_ID ('dbo.ProjectInformation','V') IS NOT NULL DROP VIEW dbo.ProjectInformation
GO 
CREATE VIEW dbo.ProjectInformation
AS
SELECT c.CompanyID,
	   c.ContractID, 
	   pAdd.City,
	   pAdd.State,
	   temp.ContractCD as template,
	   loc.LocationCD,
	   loc.Descr as LocationName,
        bAExec.AcctCD as ProjectExecutiveCD,
        bAExec.AcctName as ProjectExecutiveName,
        execEmp.UserID as ProjectExecutiveID,
        bAest.AcctCD as ProjectEstimatorCD,
        bAest.AcctName as ProjectEstimatorName,
        bAprod.AcctCD as ProjectProducerCD,
        bAprod.AcctName as ProjectProducerName,
        crUser.FullName as ProjectCreatedByUser,
        lmUser.FullName as ProjectLastModifiedByUser,
        salesAcct.AccountCD as SalesAccountCD,
        salesAcct.Description as SalesAccountDescription,
        dbo.GetProjectOpenInvoices(c.CompanyID, c.ContractID) as wattersonUnpaidInvoicesAmount,
        sum(case when pag.GroupCD = 'WIP'  then bud.CuryActualAmount   else 0 end) as WIPActual,
        sum(case when bud.[TYPE] = 'I' then bud.CuryActualAmount else 0 end) as RevenueActual,
        sum(case when bud.[TYPE] = 'E' then bud.CuryActualAmount else 0 end) as TotalExpenses,
        sum(case when bud.[TYPE] = 'E'  then bud.CuryRevisedAmount else 0 end) as RevisedBudgetedExpense,
        sum(case when bud.[TYPE] = 'I'  then bud.CuryRevisedAmount else 0 end) as RevisedBudgetedIncome,
        sum(case when pag.GroupCD = 'UNRECREV' then bud.CuryActualAmount   else 0 end) as UnrecrevActual,
        sum(case when bud.[TYPE] = 'I' then bud.ActualAmount else 0 end) as ActualIncomeSum,
        sum(case when pag.GroupCD = 'UNBILLREV' then bud.ActualAmount else 0 end) as UnBillRev
FROM
	Contract c
left join PMBudget bud on bud.CompanyID = c.CompanyID and bud.ProjectID = c.ContractID
LEFT JOIN PMAddress pAdd on pAdd.CompanyID = c.CompanyID and c.SiteAddressID = pAdd.AddressID
left join PMAccountGroup pag on pag.CompanyID = bud.CompanyID and pag.GroupID = bud.AccountGroupID
left join Contract temp on c.CompanyID = temp.CompanyID and c.TemplateID = temp.ContractID
LEFT JOIN Location loc on loc.CompanyID = c.CompanyID and c.LocationID = loc.LocationID
LEFT JOIN Users crUser on crUser.CompanyID = c.CompanyID and crUser.pkid = c.CreatedByID
LEFT JOIN Users lmUser on lmUser.CompanyID = c.CompanyID and lmUser.pkid = c.LastModifiedByID
LEFT JOIN Account salesAcct on salesAcct.CompanyID = c.CompanyID and salesAcct.AccountID = c.DefaultSalesAccountID
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
group by c.CompanyID,
       c.ContractID, 
	   pAdd.City,
	   pAdd.State,
	   temp.ContractCD,
	   loc.LocationCD,
	   loc.Descr,
        bAExec.AcctCD,
        bAExec.AcctName,
        execEmp.UserID,
        bAest.AcctCD,
        bAest.AcctName,
        bAprod.AcctCD,
        bAprod.AcctName,
        crUser.FullName,
        lmUser.FullName,
        salesAcct.AccountCD,
        salesAcct.Description