/* Relocation Expense — detail (DatawarehouseNCM) Fact: dbo.FactRelocationExpense BackOffice: frmReloExpRpt Crystal: ReloExpenseRptByTransferee.rpt DateType: T = TransactionDate, P = SentToPayroll, M = MoveComplete PaidTo: NULL = All, 1 = TE, 2 = Third party ItemID: NULL / -1 = all items Defaults mirror typical BO run: exclude denied, exclude track-only, exclude ExcludeFromStdReports. Home-sale DOTA merge uses FactHomeSaleCost separately (Pm-IncludeHomeSaleCosts). */ 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; DECLARE @ExcludeStdReports bit = 1; DECLARE @ExcludeHSCCategory bit = 0; -- 1 = exclude CategoryHomeSaleCosts / ItemHomeSaleCosts SELECT f.CorpCode, f.FormalName, f.ParentCompany, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, f.ReloPolicy, f.MoveComplete, f.DisplayType, f.ExpDetailID, f.ExpenseSheetID, f.TransactionDate, f.SentToPayroll, f.SubmittedForPmt, f.CategoryID, f.Category, f.ItemID, f.Item, f.Amount, f.PeriodStart, f.PeriodEnd, f.Comment, f.ExceptionLine, f.ExceptionAppliesTo, f.DeniedExpense, f.TrackOnly, f.PaidTo, f.PaidBy, f.Payee, f.VendorId, f.GrossUpCategory, f.WithholdingCategory, f.CategoryHomeSaleCosts, f.ItemHomeSaleCosts, f.DomesticIntl FROM dbo.FactRelocationExpense f WHERE ( (@TEFileNo IS NOT NULL AND @TEFileNo <> '' AND f.TEFileNo = @TEFileNo) OR ( (@TEFileNo IS NULL OR @TEFileNo = '') AND ( (@ParentRollup = 0 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.ParentCompany = @CorpCode OR f.CorpCode = @CorpCode)) ) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName IN ('(All Divisions)', '(All Studios)') OR f.DivisionName = @DivisionName ) AND ( @ReloPolicy IS NULL OR @ReloPolicy = '' OR @ReloPolicy = '(All Policies)' OR f.ReloPolicy = @ReloPolicy ) ) ) AND ( (@DateType = 'M' AND f.MoveComplete BETWEEN @StartDate AND @EndDate) OR (@DateType = 'P' AND f.SentToPayroll BETWEEN @StartDate AND @EndDate) OR (@DateType = 'T' AND f.TransactionDate BETWEEN @StartDate AND @EndDate) ) AND (@ItemID IS NULL OR @ItemID < 0 OR f.ItemID = @ItemID) AND (@PaidTo IS NULL OR f.PaidTo = @PaidTo) AND ( (@DeniedOnly = 1 AND f.DeniedExpense = 1) OR (@DeniedOnly = 0 AND (@IncludeDenied = 1 OR ISNULL(f.DeniedExpense, 0) = 0)) ) AND (@ExcludeTrackOnly = 0 OR ISNULL(f.TrackOnly, 0) <> 1) AND (@TrackOnly = 0 OR f.TrackOnly = 1) AND ( @ExcludeStdReports = 0 OR ( ISNULL(f.CategoryExcludeFromStdReports, 0) = 0 AND ISNULL(f.ItemExcludeFromStdReports, 0) = 0 ) ) AND ( @ExcludeHSCCategory = 0 OR ( ISNULL(f.CategoryHomeSaleCosts, 0) = 0 AND ISNULL(f.ItemHomeSaleCosts, 0) = 0 ) ) AND ( @ExceptionsOnly = 0 OR ( f.ExceptionLine = 1 AND ( f.ExceptionAppliesTo = 0 OR ((f.ExceptionAppliesTo IN (1, 3) AND ISNULL(f.GrossUpCategory, 0) = 0)) OR ((f.ExceptionAppliesTo IN (1, 2) AND ISNULL(f.GrossUpCategory, 0) = 1)) ) AND ISNULL(f.WithholdingCategory, 0) = 0 ) ) ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.TEFileNo, f.Category, f.TransactionDate, f.ExpDetailID;