/* Active Transferee — DOTA summary (transactional sketch) BO runtime: GenerateDOTA → PopulateDOTAinfo(..., Process='RECAP') → tblTEDOTARecap (Initials-scoped). Crystal Pm-DOTA joins that temp data. Cash / Accrued balances match frmGPDOTARecap.CheckFile: Accrued = SUM(Amount) WHERE CashType='ACCRUED' AND VoidStatus=0 Cash = SUM(Amount) WHERE CashType='CASH' excluding (InOut='I' AND PhsCtg LIKE 'FEE%' AND Source IN ('SOP','CM','GLR')) and excluding (InOut='O' AND PhsCtg='FEE-315') This query cannot reproduce live RAC DOTA without calling ClientDOTAMain / PopulateDOTAinfo. Prefer DW: FactTEAccountDOTA (see DW queries 02). Run on ReloAction only if tblTEDOTARecap was just populated for @Initials. */ DECLARE @Initials varchar(30) = '???'; -- LoginInfo UserInitials after GenerateDOTA DECLARE @TEFileNo varchar(14) = NULL; -- NULL = all TEs in temp for initials SELECT RTRIM(r.TEFileNo) AS TEFileNo, SUM(CASE WHEN r.CashType = 'ACCRUED' AND ISNULL(r.VoidStatus, 0) = 0 THEN r.Amount ELSE 0 END) AS AccruedAmount, SUM(CASE WHEN r.CashType = 'CASH' AND NOT ( (r.InOut = 'I' AND LEFT(r.PhsCtg, 3) = 'FEE' AND RTRIM(r.Source) IN ('SOP', 'CM', 'GLR')) OR (r.InOut = 'O' AND r.PhsCtg = 'FEE-315') ) THEN r.Amount ELSE 0 END) AS CashAmount FROM dbo.tblTEDOTARecap r WHERE r.Initials = @Initials AND r.Process = 'RECAP' AND (NULLIF(@TEFileNo, '') IS NULL OR r.TEFileNo = @TEFileNo) GROUP BY RTRIM(r.TEFileNo) HAVING SUM(CASE WHEN r.CashType = 'ACCRUED' AND ISNULL(r.VoidStatus, 0) = 0 THEN r.Amount ELSE 0 END) <> 0 OR SUM(CASE WHEN r.CashType = 'CASH' AND NOT ( (r.InOut = 'I' AND LEFT(r.PhsCtg, 3) = 'FEE' AND RTRIM(r.Source) IN ('SOP', 'CM', 'GLR')) OR (r.InOut = 'O' AND r.PhsCtg = 'FEE-315') ) THEN r.Amount ELSE 0 END) <> 0 ORDER BY TEFileNo;