--04 Relocation Expense Tax NonTax Detail.sql -- SE rewrite of BO Relocation Expense Tax/NonTax detail -- Views: udv_transaction_detail_34, udv_profile_information_view_34, udv_Expense_Reports_34 -- Note: FederalTaxClass 1/2/3 not in SEViews; Taxability / Grossed Up used as coarse proxies. DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @DivisionName varchar(30) = NULL; DECLARE @ReloPolicy varchar(100) = NULL; DECLARE @TEFileNo varchar(50) = NULL; DECLARE @DateType char(1) = 'T'; DECLARE @ExcludeTrackOnly bit = 1; DECLARE @TrackOnly bit = 0; DECLARE @ExceptionsOnly bit = 0; SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, td.[Client Name] AS ParentCompany, td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, td.[Division] AS DivisionName, td.[Policy] AS ReloPolicy, td.[Move Complete Date] AS MoveComplete, td.[Payables PO Line Number] AS ExpDetailID, td.[Transaction Date] AS TransactionDate, td.[Payroll Batch Date] AS SentToPayroll, td.[Expense Category] AS Category, td.[Expense Item] AS Item, td.[Transaction Amount] AS Amount, NULL AS CategoryFederalTaxClass, -- Not used in report NULL AS ItemFederalTaxClass, -- Not used in report NULL AS EffectiveFederalTaxClass, -- Not used in report CASE WHEN td.[Taxability] IS NULL THEN NULL WHEN td.[Taxability] LIKE '%Non%Tax%' THEN 0 ELSE td.[Transaction Amount] END AS TaxableAmount, -- approximate via Taxability CASE WHEN td.[Taxability] LIKE '%Non%Tax%' THEN td.[Transaction Amount] ELSE 0 END AS NonTaxableAmount, ISNULL(td.[Taxability], 'Unclassified') AS TaxClassLabel, CASE WHEN td.[Grossed Up] = 'Grossed Up' THEN 1 ELSE 0 END AS GrossUpCategory, NULL AS CategoryIncludeInGrossUps, -- Not used in report NULL AS ItemIncludeInGrossUps, -- Not used in report Null AS WithholdingCategory, -- Not used in report td.[Exception] AS ExceptionLine, NULL AS DeniedExpense, -- Not reporting any Denied in SE CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END AS TrackOnly, td.[Pay To] AS PaidTo, td.[Payee Name] AS Payee, td.[Comments] AS Comment FROM se_reports.dbo.[udv_transaction_detail_34] td JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON td.[File #] = pf.[File #] JOIN se_reports.dbo.[udv_payroll_detail_34] pd ON pd.[Finance Transfer UID] = td.[Finance Transfer UID] WHERE ( (@TEFileNo IS NOT NULL AND @TEFileNo <> '' AND td.[File #] = @TEFileNo) OR ( (@TEFileNo IS NULL OR @TEFileNo = '') AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) AND ( @DivisionName IS NULL OR @DivisionName = '' OR @DivisionName IN ('(All Divisions)', '(All Studios)') OR td.[Division] = @DivisionName ) AND ( @ReloPolicy IS NULL OR @ReloPolicy = '' OR @ReloPolicy = '(All Policies)' OR td.[Policy] = @ReloPolicy ) ) ) AND ( (@DateType = 'M' AND td.[Move Complete Date] BETWEEN @StartDate AND @EndDate) OR (@DateType = 'T' AND td.[Transaction Date] BETWEEN @StartDate AND @EndDate) OR (@DateType = 'P' AND td.[Payroll Batch Date] BETWEEN @StartDate AND @EndDate) ) AND (@ExcludeTrackOnly = 0 OR CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END <> 1) AND (@TrackOnly = 0 OR CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END = 1) AND (@ExceptionsOnly = 0 OR td.[Exception] = 'Yes') ORDER BY td.[Corp Code], td.[Last Name], td.[First Name], td.[File #], td.[Transaction Date];