/* Relocation Expense — Tax/NonTax Detail (ReloAction) BackOffice: frmReloExpRpt (lblReportType = TaxNonTax) Crystal: ReloExpTaxNonDetail.rpt Same expense-line sources as By Transferee, with Tax / NonTax amount split from FederalTaxClass (category/item maintenance): 1 = Non-Taxable 2 = Tax Deductible 3 = Taxable Expense EffectiveFederalTaxClass = ItemFederalTaxClass when non-zero, else CategoryFederalTaxClass. Pm-PayeeFilter: '' = All, 'TE' = PaidTo 1, 'THIRD' = PaidTo 2. 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 @DateType char(1) = 'T'; -- T | P | M 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; 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, ed.ExpDetailID, ed.TransactionDate, ed.SentToPayroll, ed.CategoryID, ec.Category, ed.ItemID, ei.Item, ed.Amount, ec.FederalTaxClass AS CategoryFederalTaxClass, ei.FederalTaxClass AS ItemFederalTaxClass, CASE WHEN ISNULL(ei.FederalTaxClass, 0) <> 0 THEN ei.FederalTaxClass ELSE ec.FederalTaxClass END AS EffectiveFederalTaxClass, CASE WHEN CASE WHEN ISNULL(ei.FederalTaxClass, 0) <> 0 THEN ei.FederalTaxClass ELSE ec.FederalTaxClass END = 1 THEN 0 ELSE ed.Amount END AS TaxableAmount, CASE WHEN CASE WHEN ISNULL(ei.FederalTaxClass, 0) <> 0 THEN ei.FederalTaxClass ELSE ec.FederalTaxClass END = 1 THEN ed.Amount ELSE 0 END AS NonTaxableAmount, CASE WHEN CASE WHEN ISNULL(ei.FederalTaxClass, 0) <> 0 THEN ei.FederalTaxClass ELSE ec.FederalTaxClass END = 1 THEN 'NonTaxable' WHEN CASE WHEN ISNULL(ei.FederalTaxClass, 0) <> 0 THEN ei.FederalTaxClass ELSE ec.FederalTaxClass END = 2 THEN 'TaxDeductible' WHEN CASE WHEN ISNULL(ei.FederalTaxClass, 0) <> 0 THEN ei.FederalTaxClass ELSE ec.FederalTaxClass END = 3 THEN 'TaxableExpense' ELSE 'Unclassified' END AS TaxClassLabel, ec.GrossUpCategory, ec.IncludeInGrossUps AS CategoryIncludeInGrossUps, ei.IncludeInGrossUps AS ItemIncludeInGrossUps, ec.WithholdingCategory, ed.ExceptionLine, ed.ExceptionAppliesTo, ed.DeniedExpense, ed.TrackOnly, ed.PaidTo, ed.Payee, CONVERT(varchar(max), ed.Comment) AS Comment 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 ( @PayeeFilter = '' OR @PayeeFilter IS NULL OR (@PayeeFilter = 'TE' AND ed.PaidTo = 1) OR (@PayeeFilter = 'THIRD' AND ed.PaidTo = 2) ) 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;