/* Active Transferee — DOTA summary (DatawarehouseNCM) Pm-DOTA / Include DOTA Summary. Balances from FactTEAccountDOTA (ClientDOTAMain RECAP lineage; scoped to active TEs at populate). Cash / Accrued match frmGPDOTARecap.CheckFile FEE exclusions. Run on DatawarehouseNCM after populate. */ DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @TEFileNo varchar(14) = NULL; ;WITH ActiveTE AS ( SELECT DISTINCT TEFileNo, CorpCode, ParentCompany, FormalName, TELastName, TEFirstName FROM dbo.FactActiveTransferee WHERE ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND CorpCode = @CorpCode) ) AND (NULLIF(@TEFileNo, '') IS NULL OR TEFileNo = @TEFileNo) ), DotaBal AS ( SELECT RTRIM(d.TEFileNo) AS TEFileNo, SUM(CASE WHEN d.CashType = 'ACCRUED' AND ISNULL(d.VoidStatus, 0) = 0 THEN d.Amount ELSE 0 END) AS AccruedAmount, SUM(CASE WHEN d.CashType = 'CASH' AND NOT ( (d.InOut = 'I' AND LEFT(d.PhsCtg, 3) = 'FEE' AND RTRIM(d.Source) IN ('SOP', 'CM', 'GLR')) OR (d.InOut = 'O' AND d.PhsCtg = 'FEE-315') ) THEN d.Amount ELSE 0 END) AS CashAmount FROM dbo.FactTEAccountDOTA d INNER JOIN ActiveTE a ON d.TEFileNo = a.TEFileNo GROUP BY RTRIM(d.TEFileNo) ) SELECT a.TEFileNo, a.CorpCode, a.FormalName, a.TELastName, a.TEFirstName, ISNULL(b.CashAmount, 0) AS CashAmount, ISNULL(b.AccruedAmount, 0) AS AccruedAmount, ISNULL(b.CashAmount, 0) + ISNULL(b.AccruedAmount, 0) AS CombinedDOTA FROM ActiveTE a LEFT JOIN DotaBal b ON a.TEFileNo = b.TEFileNo ORDER BY a.CorpCode, a.TEFileNo;