/* Relocation Expense — Tax/NonTax Detail (DatawarehouseNCM) Fact: dbo.FactRelocationExpense BackOffice: frmReloExpRpt (TaxNonTax) / Crystal: ReloExpTaxNonDetail.rpt FederalTaxClass: 1=NonTaxable, 2=TaxDeductible, 3=TaxableExpense EffectiveFederalTaxClass prefers ItemFederalTaxClass when non-zero. TaxableAmount / NonTaxableAmount mirror Crystal Tax/NonTax columns. */ 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 @DateType char(1) = 'T'; DECLARE @PayeeFilter varchar(10) = ''; -- '' | TE | THIRD 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 @SummaryFmt bit = 0; -- Pm-SummaryFmt: 1 = TE rollup SELECT f.CorpCode, f.FormalName, f.ParentCompany, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, f.ReloPolicy, f.MoveComplete, f.ExpDetailID, f.TransactionDate, f.SentToPayroll, f.Category, f.Item, f.Amount, f.CategoryFederalTaxClass, f.ItemFederalTaxClass, CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END AS EffectiveFederalTaxClass, CASE WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 1 THEN 0 ELSE f.Amount END AS TaxableAmount, CASE WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 1 THEN f.Amount ELSE 0 END AS NonTaxableAmount, CASE WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 1 THEN 'NonTaxable' WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 2 THEN 'TaxDeductible' WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 3 THEN 'TaxableExpense' ELSE 'Unclassified' END AS TaxClassLabel, f.GrossUpCategory, f.CategoryIncludeInGrossUps, f.ItemIncludeInGrossUps, f.WithholdingCategory, f.ExceptionLine, f.DeniedExpense, f.TrackOnly, f.PaidTo, f.Payee, f.Comment 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 ( @PayeeFilter = '' OR @PayeeFilter IS NULL OR (@PayeeFilter = 'TE' AND f.PaidTo = 1) OR (@PayeeFilter = 'THIRD' AND f.PaidTo = 2) ) 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 ( @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 ) ) AND @SummaryFmt = 0 ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.TEFileNo, f.Category, f.TransactionDate, f.ExpDetailID; -- Summary rollup (Pm-SummaryFmt = True uses same Crystal with summary layout) SELECT f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName, COUNT(*) AS LineCount, SUM(f.Amount) AS TotalAmount, SUM(CASE WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 1 THEN 0 ELSE f.Amount END) AS TaxableAmount, SUM(CASE WHEN CASE WHEN ISNULL(f.ItemFederalTaxClass, 0) <> 0 THEN f.ItemFederalTaxClass ELSE f.CategoryFederalTaxClass END = 1 THEN f.Amount ELSE 0 END) AS NonTaxableAmount FROM dbo.FactRelocationExpense f WHERE ( (@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 ( (@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 ( @PayeeFilter = '' OR @PayeeFilter IS NULL OR (@PayeeFilter = 'TE' AND f.PaidTo = 1) OR (@PayeeFilter = 'THIRD' AND f.PaidTo = 2) ) AND ( @ExcludeStdReports = 0 OR ( ISNULL(f.CategoryExcludeFromStdReports, 0) = 0 AND ISNULL(f.ItemExcludeFromStdReports, 0) = 0 ) ) AND (@ExcludeTrackOnly = 0 OR ISNULL(f.TrackOnly, 0) <> 1) AND ( (@DeniedOnly = 1 AND f.DeniedExpense = 1) OR (@DeniedOnly = 0 AND (@IncludeDenied = 1 OR ISNULL(f.DeniedExpense, 0) = 0)) ) AND @SummaryFmt = 1 GROUP BY f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.DivisionName ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.TEFileNo;