AcumaticaSQLQueriesAndFunctions/SalesActual.sql

DROP VIEW IF EXISTS SalesActual
GO 
Create View SalesActual
as
with CustomerBusinessDevOwner
as
(
SELECT 
b.CompanyID, 
ROW_NUMBER() OVER(PARTITION BY c.CompanyID ORDER BY c.CompanyID) AS rc,
c.BAccountID as CustomerBAccountID,
e.BAccountID as EmployeeBAccountID , 
b.AcctCD as CustomerCD,
e.AcctCD as EmployeeCD, 
e.AcctName as EmployeeName 
from Customer c 
inner join BAccount b on c.CompanyID = b.CompanyID and c.BAccountID = b.BAccountID 
left JOIN BAccountKvExt bke on b.CompanyID = bke.CompanyID and  b.NoteID = bke.RecordID 
left join BAccount e on bke.CompanyID =e.CompanyID and bke.ValueString = e.AcctCD 
)
SELECT c.CompanyID, 
	   c.rc,
       c.EmployeeBAccountID,
	   c.EmployeeName, 
	   CASE 
        WHEN LAG(c.EmployeeBAccountID) OVER (ORDER BY c.EmployeeBAccountID, c.CustomerBAccountID) = c.EmployeeBAccountID THEN ''
        ELSE c.EmployeeName
       END AS EmployeeNameGroup,
       c.CustomerBAccountID,
	   c.CustomerCD, 
	   dbo.CountCustomerContracts(dbo.GetFirstDayOfPeriod(GETDATE(),'month'),GETDATE(),c.CompanyID,c.CustomerBAccountID) as WorkOrderCountMTD,
	   dbo.SumCustomerInvoiceAmt(c.CompanyID, c.CustomerBAccountID, dbo.GetFirstDayOfPeriod(GETDATE(),'month'),GETDATE()) as AmountInvoicedMTD,
	   dbo.SumCustomerInvoicePayments(c.CompanyID, c.CustomerBAccountID,dbo.GetFirstDayOfPeriod(GETDATE(),'month'),GETDATE()) as AmountReceivedMTD,
	   dbo.CountCustomerContracts(dbo.GetFirstDayOfPeriod(GETDATE(),'quarter'),GETDATE(),c.CompanyID,c.CustomerBAccountID) as WorkOrderCountQTD,
	   dbo.SumCustomerInvoiceAmt(c.CompanyID, c.CustomerBAccountID, dbo.GetFirstDayOfPeriod(GETDATE(),'quarter'),GETDATE()) as AmountInvoicedQTD,
	   dbo.SumCustomerInvoicePayments(c.CompanyID, c.CustomerBAccountID,dbo.GetFirstDayOfPeriod(GETDATE(),'quarter'),GETDATE()) as AmountReceivedQTD,
	   dbo.CountCustomerContracts(dbo.GetFirstDayOfPeriod(GETDATE(),'year'),GETDATE(),c.CompanyID,c.CustomerBAccountID) as WorkOrderCountYTD,
	   dbo.SumCustomerInvoiceAmt(c.CompanyID, c.CustomerBAccountID, dbo.GetFirstDayOfPeriod(GETDATE(),'year'),GETDATE()) as AmountInvoicedYTD,
	   dbo.SumCustomerInvoicePayments(c.CompanyID, c.CustomerBAccountID,dbo.GetFirstDayOfPeriod(GETDATE(),'year'),GETDATE()) as AmountReceivedYTD,
	   dbo.GetFirstDayOfPeriod(GETDATE(),'month')as MonthDateUsed,
	   dbo.GetFirstDayOfPeriod(GETDATE(),'quarter')as QuarterDateUsed,
	   dbo.GetFirstDayOfPeriod(GETDATE(),'year')as YearDateUsed
	   FROM 
    	CustomerBusinessDevOwner c

GO 
DROP VIEW IF EXISTS InvoicePaymentAudit
GO 
Create View InvoicePaymentAudit
as
SELECT a.CompanyID,
	   a.CustomerID,
	   b.acctcd as CustomerCD,
	   c.contractID,
	   c.ContractCD,
    	   a.AdjdRefNbr as InvoiceNumber, 
    	   a.AdjdDocType as ParentDocType,
    	   a.AdjgRefNbr as ApplicationNumber, 
    	   a.AdjgDocType as ApplicationType,
    	   a.AdjgDocDate as PaymentDate, 
    	   a.CuryAdjdAmt as AmountPaid,
    	   case when dbo.GetFirstDayOfPeriod(GETDATE(),'month') <= a.AdjgDocDate then 1 else 0 end as InCurrentMonth,
    	   case when dbo.GetFirstDayOfPeriod(GETDATE(),'quarter') <= a.AdjgDocDate then 1 else 0 end as InCurrentQuarter,
    	   case when dbo.GetFirstDayOfPeriod(GETDATE(),'year') <= a.AdjgDocDate then 1 else 0 end as InCurrentYear,
    	   dbo.GetFirstDayOfPeriod(GETDATE(),'month') as MonthStartUsed,
    	   dbo.GetFirstDayOfPeriod(GETDATE(),'quarter') as QuarterStartUsed,
    	   dbo.GetFirstDayOfPeriod(GETDATE(),'year') as YearStartUsed
FROM ARAdjust a 
inner join ARInvoice i on a.CompanyID = i.CompanyID and i.DocType = a.AdjdDocType and i.RefNbr = a.AdjdRefNbr
left join BAccount b on a.CompanyID = b.CompanyID and a.CustomerID = b.BaccountID
left join Contract c on i.CompanyID = c.CompanyID and c.ContractID = i.ProjectID
where a.AdjdDocType = 'INV'
and a.AdjgDocType = 'PMT'
GO
DROP VIEW IF EXISTS InvoiceAmountAudit
GO 
Create View InvoiceAmountAudit
as
SELECT a.CompanyID, 
	   a.DocType,  
	   a.DocDate,
	   p.ContractID as ProjectID,
	   p.ContractCD as ProjectCD,
	   c.BAccountID as CustomerBAccountID, 
	   c.AcctCD as CustomerName,
	   a.RefNbr as InvoiceRefNbr,
	   a.OrigDocAmt as InvoiceAmount,
	   i.CuryGoodsExtPriceTotal,
	   i.CuryLineTotal,
	   i.CuryDiscTot,
	   a.CuryDocBal,
	   i.LineDiscTotal,
	   i.CuryTaxTotal, 
	   i.PaymentTotal,
       case when dbo.GetFirstDayOfPeriod(GETDATE(),'month') <= a.DocDate then 1 else 0 end as InCurrentMonth,
       case when dbo.GetFirstDayOfPeriod(GETDATE(),'quarter') <= a.DocDate then 1 else 0 end as InCurrentQuarter,
       case when dbo.GetFirstDayOfPeriod(GETDATE(),'year') <= a.DocDate then 1 else 0 end as InCurrentYear,
       dbo.GetFirstDayOfPeriod(GETDATE(),'month') as MonthStartUsed,
       dbo.GetFirstDayOfPeriod(GETDATE(),'quarter') as QuarterStartUsed,
       dbo.GetFirstDayOfPeriod(GETDATE(),'year') as YearStartUsed
from ARRegister a
join ARInvoice i on a.CompanyID = i.CompanyID  and a.DocType = i.DocType and a.RefNbr = i.RefNbr 
left join BAccount c on a.CompanyID = c.CompanyID and a.CustomerID = c.BAccountID
left join Contract p on i.CompanyID = p.CompanyID and i.ProjectID = p.ContractID 
WHERE a.DocType = 'INV'
GO
DROP VIEW IF EXISTS ContractCustomerAudit
GO 
Create View ContractCustomerAudit
as
SELECT c.CompanyID, 
	   c.ContractID, 
	   c.ContractCD, 
	   c.CustomerID, 
	   b.AcctCD as CustomerName, 
	   c.CreatedDateTime, 
	   c.ActivationDate,
	   case when dbo.GetFirstDayOfPeriod(GETDATE(),'month') <= c.CreatedDateTime then 1 else 0 end as CreatedInCurrentMonth,
       case when dbo.GetFirstDayOfPeriod(GETDATE(),'quarter') <= c.CreatedDateTime then 1 else 0 end as CreatedInCurrentQuarter,
       case when dbo.GetFirstDayOfPeriod(GETDATE(),'year') <= c.CreatedDateTime then 1 else 0 end as CreatedInCurrentYear,
       dbo.GetFirstDayOfPeriod(GETDATE(),'month') as MonthStartUsed,
       dbo.GetFirstDayOfPeriod(GETDATE(),'quarter') as QuarterStartUsed,
       dbo.GetFirstDayOfPeriod(GETDATE(),'year') as YearStartUsed
FROM Contract c 
join BAccount b  on c.CompanyID = b.CompanyID and c.CustomerID = b.BAccountID