AcumaticaSQLQueriesAndFunctions/GLTranRevenueSums.sql

IF OBJECT_ID ('dbo.GLTranRevenueSumsWithPeriod','V') IS NOT NULL DROP VIEW dbo.GLTranRevenueSumsWithPeriod
GO 
CREATE VIEW dbo.GLTranRevenueSumsWithPeriod
AS
SELECT t.CompanyID, 
       t.ProjectID, 
       c.ContractCD, 
	   c.NoteID,
       cust.BAccountID as CustomerID,
       cust.AcctCD as CustomerCD,
       cust.AcctName as CustomerName,
       br.BranchID as BranchKey,
		br.BranchCD as BranchCD,
		ba.AcctName as BranchName,
		dbo.GetTopContractUserDefinedFieldDescription(t.CompanyID, c.NoteID, 'AttributeCategory') as JobCategory,
		dbo.GetTopContractUserDefinedFieldDescription(t.CompanyID, c.NoteID, 'AttributejobStatus') as JobStatus,
		dbo.GetTopContractUserDefinedFieldDescription(t.CompanyID, c.NoteID, 'AttributeProjectGRP') as ProjectGroup,
		dbo.GetContractUserDefinedField(t.CompanyID, c.NoteID, 'AttributeESTCompl', 0)  as EstimatedComplete,
		dbo.GetContractUserDefinedField(t.CompanyID, c.NoteID, 'AttributePO', 0)  as CustomerPO,
		dbo.GetRevisedBudgetAmount(t.CompanyID,t.ProjectID,'I') as RevisedBudgetedincome,
		dbo.GetRevisedBudgetAmount(t.CompanyID,t.ProjectID,'E') as RevisedBudgetedExpense,
		dbo.GetTransactions(t.CompanyID, t.ProjectID, 1, 1,0) as TotalExpenses,
		c.status as ProjectStatus,
		c.CreatedDateTime as ProjectCreatedDateTime,
		c.LastModifiedDateTime as ProjectLastModifiedDateTime,
		bAExec.AcctCD as ExecutiveNumber,
        bAExec.AcctName as ExecutiveName,
        execEmp.userID as ExecutivePKID,
        lmu.PKID as LastModifiedByKey,
        lmu.Username as LastModifiedUserName,
        lmu.FullName as LastModifiedName,
       t.FinPeriodID,
       SUM(CASE 
               WHEN a.AccountCD = '4001' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0)
               ELSE 0 
           END) AS BilledRevenue4001,  -- "Billed Revenue - 4001"
       SUM(CASE 
               WHEN a.AccountCD = '4002' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0)
               ELSE 0 
           END) AS TMUnbilledRevenue4002,  -- "T&M Unbilled Revenue - 4002"
       SUM(CASE 
               WHEN a.AccountCD = '4000' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0)
               ELSE 0 
           END) AS LumpSumWIPRevenue4000,  -- "Lump Sum WIP Revenue - 4000"
       SUM(CASE 
               WHEN a.AccountCD = '4003' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0)
               ELSE 0 
           END) AS BillingsLumpSum4003,  -- "Billings Lump Sum - 4003"
       SUM(CASE 
               WHEN TRY_CAST(a.AccountCD AS INT) BETWEEN 5000 AND 5999 THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0)
               ELSE 0 
           END) AS TotalExpensesCOGS,  -- "Total Expenses - COGS" 
       SUM(CASE WHEN a.AccountCD = '4007' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0) ELSE 0 END) AS ClientDiscounts4007,
       SUM(CASE WHEN a.AccountCD = '4500' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0) ELSE 0 END) AS OtherIncome4500,		   
       SUM(CASE WHEN a.AccountCD = '4502' THEN COALESCE(t.CreditAmt, 0) - COALESCE(t.DebitAmt, 0) ELSE 0 END) AS Refunds4502,		
       SUM(CASE WHEN a.AccountCD = '5000' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCLabor5000,
       SUM(CASE WHEN a.AccountCD = '5001' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCLaborBurden5001,
       SUM(CASE WHEN a.AccountCD = '5010' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCSubcontractor5010,
       SUM(CASE WHEN a.AccountCD = '5020' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCMaterials5020,
       SUM(CASE WHEN a.AccountCD = '5030' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCEquipmentInternal5030,
       SUM(CASE WHEN a.AccountCD = '5040' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCEquipmentExternal5040,
       SUM(CASE WHEN a.AccountCD = '5041' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCConsultant5041,
       SUM(CASE WHEN a.AccountCD = '5050' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCTravel5050,
       SUM(CASE WHEN a.AccountCD = '5055' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCOther5055,
       SUM(CASE WHEN a.AccountCD = '5056' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS JCNonAllocating5056,
       SUM(CASE WHEN a.AccountCD = '5099' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS WIPCOGS5099,
       SUM(CASE WHEN a.AccountCD = '5100' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS ShopSuppliesIndirect5100,
       SUM(CASE WHEN a.AccountCD = '5101' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS ShopExpensesSmallTools5101,
       SUM(CASE WHEN a.AccountCD = '5102' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS ShopExpenseUniforms5102,
       SUM(CASE WHEN a.AccountCD = '5103' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS ShopIndirectLabor5103,
       SUM(CASE WHEN a.AccountCD = '5104' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS DirectLabor5104,
       SUM(CASE WHEN a.AccountCD = '5105' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS IndirectQuotingCosts5105,
       SUM(CASE WHEN a.AccountCD = '5108' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS PassThroughCost5108,
       SUM(CASE WHEN a.AccountCD = '5109' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS PurchasePriceVariance5109,
       SUM(CASE WHEN a.AccountCD = '5110' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS WarrantyWork5110,
       SUM(CASE WHEN a.AccountCD = '5111' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS EstSubcontractorCosts5111,
       SUM(CASE WHEN a.AccountCD = '5115' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS CatTravelExpenses5115,
       SUM(CASE WHEN a.AccountCD = '5201' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS DiscountsGiven5201,
       SUM(CASE WHEN a.AccountCD = '5202' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS DiscountsTaken5202,
       SUM(CASE WHEN a.AccountCD = '5210' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS ClientBillingExpense5210,
       SUM(CASE WHEN a.AccountCD = '5300' THEN COALESCE(t.DebitAmt, 0) - COALESCE(t.CreditAmt, 0) ELSE 0 END) AS BadDebt5300
FROM  GLTran t
left JOIN  Contract c ON t.CompanyID = c.CompanyID  AND t.ProjectID = c.ContractID
left JOIN  Account a  ON t.CompanyID = a.CompanyID AND t.AccountID = a.AccountID
LEFT join BAccount cust on cust.CompanyID = c.CompanyID and cust.BAccountID = c.CustomerID
LEFT JOIN Branch br on br.CompanyID = c.CompanyID and br.BranchID = c.DefaultBranchID
LEFT join BACCount ba on ba.CompanyID = br.CompanyID and ba.BAccountID = br.BACCountID
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 Users lmu on lmu.CompanyID = c.CompanyID and lmu.PKID = c.LastModifiedByID
WHERE (a.AccountCD IN ('4001', '4002', '4000', '4003', '4007', '4500', '4502') 
       OR TRY_CAST(a.AccountCD AS INT) BETWEEN 5000 AND 5999)
GROUP BY t.CompanyID,
		 t.ProjectID, 
		 c.ContractCD, 
		 c.CreatedDateTime, 
		 c.LastModifiedDateTime,
		 c.NoteID, 
		 c.status, 
		 t.FinPeriodID,
		 cust.BAccountID,
		 cust.AcctCD, 
		 cust.AcctName, 
		 br.BranchID, 
		 br.BranchCD, 
		 ba.AcctName,
		 bAExec.AcctCD,
         bAExec.AcctName,
         execEmp.userID,
         lmu.PKID,
         lmu.Username,
         lmu.FullName