AcumaticaSQLQueriesAndFunctions/SalesByBD.sql

DROP VIEW IF EXISTS SalesByBD
GO 
Create View SalesByBD
as
SELECT b.CompanyID,
      b.EmployeeBAccountID,
       b.EmployeeCD,
       b.EmployeeName,
       b.CustomerBAccountID, 
       b.CustomerCD, 
       c.ContractID, 
       c.ContractCD, 
       c.CreatedDateTime, 
       c.ActivationDate,
       dbo.GetBudgetAmount(c.CompanyID, c.ContractID, 'I',dbo.GetFirstDayOfPeriod(GETDATE(),'month')) as BudgetedRevenueMTD,
       dbo.GetBudgetAmount(c.CompanyID, c.ContractID, 'I',dbo.GetFirstDayOfPeriod(GETDATE(),'quarter')) as BudgetedRevenueQTD,
       dbo.GetBudgetAmount(c.CompanyID, c.ContractID, 'I',dbo.GetFirstDayOfPeriod(GETDATE(),'year')) as BudgetedRevenueYTD,
       dbo.GetBudgetAmount(c.CompanyID, c.ContractID, 'I',c.CreatedDateTime) as BudgetedRevenuePTD,
       dbo.SumProjectInvoiceAmt(c.CompanyID, c.ContractID,dbo.GetFirstDayOfPeriod(GETDATE(),'month'), GETDATE() ) as AmountInvoicedMTD,
       dbo.SumProjectInvoiceAmt(c.CompanyID, c.ContractID,dbo.GetFirstDayOfPeriod(GETDATE(),'quarter'), GETDATE() ) as AmountInvoicedQTD,
       dbo.SumProjectInvoiceAmt(c.CompanyID, c.ContractID,dbo.GetFirstDayOfPeriod(GETDATE(),'year'), GETDATE() ) as AmountInvoicedYTD,
       dbo.SumProjectInvoiceAmt(c.CompanyID, c.ContractID,c.CreatedDateTime, GETDATE() ) as AmountInvoicedPTD,
       dbo.SumProjectInvoicePayments(c.CompanyID, c.ContractID,dbo.GetFirstDayOfPeriod(GETDATE(),'month'), GETDATE() ) as AmountRecievedMTD,
       dbo.SumProjectInvoicePayments(c.CompanyID, c.ContractID,dbo.GetFirstDayOfPeriod(GETDATE(),'month'), GETDATE() ) as AmountRecievedQTD,
       dbo.SumProjectInvoicePayments(c.CompanyID, c.ContractID,dbo.GetFirstDayOfPeriod(GETDATE(),'month'), GETDATE() ) as AmountRecievedYTD,
       dbo.SumProjectInvoicePayments(c.CompanyID, c.ContractID,CreatedDateTime, GETDATE() ) as AmountRecievedPTD
from CustomerBusinessDevOwner b
join Contract c on b.CompanyID = c.CompanyID and c.CustomerID = b.CustomerBAccountID