AcumaticaSQLQueriesAndFunctions/ActualRevenue.sql

IF OBJECT_ID ('dbo.ActualRevenue','V') IS NOT NULL DROP VIEW dbo.ActualRevenue
GO 
CREATE VIEW dbo.ActualRevenue
AS
SELECT ROW_NUMBER() OVER (
        ORDER BY 
            p.CompanyID, 
            p.ProjectID
    ) AS RowKey,
    p.CompanyID, 
    p.ProjectID, 
    c.ContractCD,
    ABS(SUM(CASE 
            WHEN p.OffsetAccountGroupID = 24 AND p.AccountGroupID = 25 THEN p.Amount * -1
            WHEN p.OffsetAccountGroupID = 25 AND p.AccountGroupID = 24 THEN p.Amount
            ELSE p.Amount
        END)) as [ActulRevenueCompute],
    SUM(p.Amount) AS [Amount], 
    SUM(p.TranCuryAmount) AS [TranCuryAmount], 
    SUM(p.ProjectCuryAmount) AS [ProjectCuryAmount], 
    SUM(p.ProjectCuryInvoicedAmount) AS [ProjectCuryInvoicedAmount],
    SUM(p.InvoicedAmount) AS [InvoicedAmount]
FROM PMTran p 
join Contract c on c.CompanyID = p.CompanyID and c.ContractID = p.ProjectID 
WHERE p.AccountGroupID IN (24)
OR p.OffsetAccountGroupID IN (24)
GROUP BY 
    p.CompanyID, 
    p.ProjectID,
    c.ContractCD