/* Relocation Expense — By Transferee Detail BackOffice: frmReloExpRpt Crystal: ReloExpenseRptByTransferee.rpt Source: ReloAction expense sheets / detail + category / item + client. Category selection in BO uses tblTERptTempFile (user Initials). This sample defaults to all non-excluded categories (same as selecting All Categories). DateType: T = TransactionDate, P = SentToPayroll, M = MoveComplete PaidTo: NULL = All, 1 = TE, 2 = Third party Home-sale DOTA amounts are not in this extract; BO merges via TEHomeSaleCostsFinal into tblTmpRptAmount when Pm-IncludeHomeSaleCosts = True (see FactHomeSaleCost in DW). Run on ReloAction. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = all DECLARE @ParentRollup bit = 0; DECLARE @DivisionName varchar(30) = NULL; -- NULL / '' / '(All Divisions)' = all DECLARE @ReloPolicy varchar(50) = NULL; -- NULL / '' / '(All Policies)' = all DECLARE @TEFileNo varchar(14) = NULL; -- NULL / '' = all DECLARE @ItemID int = NULL; -- NULL / -1 = all DECLARE @DateType char(1) = 'T'; -- T | P | M DECLARE @PaidTo smallint = NULL; -- NULL = all; 1 = TE; 2 = Third DECLARE @IncludeDenied bit = 0; DECLARE @DeniedOnly bit = 0; DECLARE @ExcludeTrackOnly bit = 1; -- Pm-NoTrack DECLARE @TrackOnly bit = 0; -- Pm-TrackOnly DECLARE @ExceptionsOnly bit = 0; -- Pm-ExcptOnly SELECT RTRIM(m1.TEFileNo) AS TEFileNo, RTRIM(m1.CorpCode) AS CorpCode, RTRIM(cl.ParentCompany) AS ParentCompany, cl.FormalName, m1.TELastName, m1.TEFirstName, m1.DivisionName, m1.ReloPolicy, m1.MoveComplete, m1.DisplayType, ed.ExpDetailID, ed.ExpenseSheetID, ed.TransactionDate, ed.SentToPayroll, ed.SubmittedForPmt, ed.CategoryID, ec.Category, ed.ItemID, ei.Item, ed.Amount, ed.PeriodStart, ed.PeriodEnd, CONVERT(varchar(max), ed.Comment) AS Comment, ed.ExceptionLine, ed.ExceptionAppliesTo, ed.DeniedExpense, ed.TrackOnly, ed.PaidTo, ed.PaidBy, ed.Payee, RTRIM(ed.VENDORID) AS VendorId, ec.GrossUpCategory, ec.WithholdingCategory, ec.ExcludeFromStdReports AS CategoryExcludeFromStdReports, ec.HomeSaleCosts AS CategoryHomeSaleCosts, ei.ExcludeFromStdReports AS ItemExcludeFromStdReports, ei.HomeSaleCosts AS ItemHomeSaleCosts 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 ) ) ORDER BY m1.CorpCode, m1.TELastName, m1.TEFirstName, m1.TEFileNo, ec.Category, ed.TransactionDate, ed.ExpDetailID;