/* Relocation Expense — By Transferee Summary BackOffice: frmReloExpRpt Crystal: ReloExpenseRptByTransfereeSummary.rpt Same filters as the detail extract, rolled up by TE / category. Run on ReloAction. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @DivisionName varchar(30) = NULL; DECLARE @ReloPolicy varchar(50) = NULL; DECLARE @TEFileNo varchar(14) = NULL; DECLARE @ItemID int = NULL; DECLARE @DateType char(1) = 'T'; DECLARE @PaidTo smallint = NULL; DECLARE @IncludeDenied bit = 0; DECLARE @DeniedOnly bit = 0; DECLARE @ExcludeTrackOnly bit = 1; DECLARE @TrackOnly bit = 0; DECLARE @ExceptionsOnly bit = 0; SELECT RTRIM(m1.TEFileNo) AS TEFileNo, RTRIM(m1.CorpCode) AS CorpCode, cl.FormalName, m1.TELastName, m1.TEFirstName, m1.DivisionName, m1.ReloPolicy, ed.CategoryID, ec.Category, COUNT(*) AS LineCount, SUM(ed.Amount) AS TotalAmount FROM dbo.tblTEExpenseSheets es WITH (NOLOCK) INNER JOIN dbo.tblTEMain1 m1 ON m1.TEFileNo = es.TEFileNo INNER JOIN dbo.tblCorpClient cl ON m1.CorpCode = cl.CorpCode INNER JOIN dbo.tblTEExpenseDetail ed ON es.ExpenseSheetID = ed.ExpenseSheetID INNER JOIN dbo.tblExpenseCategory ec ON ed.CategoryID = ec.CategoryID INNER JOIN dbo.tblExpenseItem ei ON ed.ItemID = ei.ItemID WHERE ec.ExcludeFromStdReports = 0 AND ei.ExcludeFromStdReports = 0 AND ( (@TEFileNo IS NOT NULL AND @TEFileNo <> '' AND m1.TEFileNo = @TEFileNo) OR ( (@TEFileNo IS NULL OR @TEFileNo = '') AND ( (@ParentRollup = 0 AND (@CorpCode IS NULL OR @CorpCode = '' OR m1.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode IS NULL OR @CorpCode = '' OR cl.ParentCompany = @CorpCode OR m1.CorpCode = @CorpCode)) ) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName IN ('(All Divisions)', '(All Studios)') OR m1.DivisionName = @DivisionName ) AND ( @ReloPolicy IS NULL OR @ReloPolicy = '' OR @ReloPolicy = '(All Policies)' OR m1.ReloPolicy = @ReloPolicy ) ) ) AND ( (@DateType = 'M' AND m1.MoveComplete BETWEEN @StartDate AND @EndDate) OR (@DateType = 'P' AND ed.SentToPayroll BETWEEN @StartDate AND @EndDate) OR (@DateType = 'T' AND ed.TransactionDate BETWEEN @StartDate AND @EndDate) ) AND (@ItemID IS NULL OR @ItemID < 0 OR ed.ItemID = @ItemID) AND (@PaidTo IS NULL OR ed.PaidTo = @PaidTo) AND ( (@DeniedOnly = 1 AND ed.DeniedExpense = 1) OR (@DeniedOnly = 0 AND (@IncludeDenied = 1 OR ISNULL(ed.DeniedExpense, 0) = 0)) ) AND (@ExcludeTrackOnly = 0 OR ISNULL(ed.TrackOnly, 0) <> 1) AND (@TrackOnly = 0 OR ed.TrackOnly = 1) AND ( @ExceptionsOnly = 0 OR ( ed.ExceptionLine = 1 AND ( ed.ExceptionAppliesTo = 0 OR ((ed.ExceptionAppliesTo IN (1, 3) AND ISNULL(ec.GrossUpCategory, 0) = 0)) OR ((ed.ExceptionAppliesTo IN (1, 2) AND ISNULL(ec.GrossUpCategory, 0) = 1)) ) AND ISNULL(ec.WithholdingCategory, 0) = 0 ) ) GROUP BY RTRIM(m1.TEFileNo), RTRIM(m1.CorpCode), cl.FormalName, m1.TELastName, m1.TEFirstName, m1.DivisionName, m1.ReloPolicy, ed.CategoryID, ec.Category ORDER BY CorpCode, TELastName, TEFirstName, TEFileNo, Category;