--01 Relocation Expense Detail.sql -- SE rewrite of BO Relocation Expense detail (FactRelocationExpense) -- Views: udv_transaction_detail_34, udv_profile_information_view_34, udv_Expense_Reports_34 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'; -- T=TransactionDate; M=MoveComplete; P=SentToPayroll unable DECLARE @ExcludeTrackOnly bit = 1; DECLARE @TrackOnly bit = 0; DECLARE @ExceptionsOnly bit = 0; SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, NULL AS ParentCompany, -- NOT USED IN SE 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, NULL AS DisplayType, -- NOT USED IN SE td.[Payables PO Line Number] AS ExpDetailID, td.[Expense Report ID] AS ExpenseSheetID, td.[Transaction Date] AS TransactionDate, NULL AS SentToPayroll, -- unable to find er.[Submitted Date] AS SubmittedForPmt, NULL AS CategoryID, -- unable to find (BO Item/Category IDs) td.[Expense Category] AS Category, NULL AS ItemID, -- unable to find td.[Expense Item] AS Item, td.[Transaction Amount] AS Amount, er.[Report From Date] AS PeriodStart, -- header grain approximate er.[Report To Date] AS PeriodEnd, td.[Comments] AS Comment, td.[Exception] AS ExceptionLine, NULL AS ExceptionAppliesTo, -- unable to find NULL AS DeniedExpense, -- unable to find CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END AS TrackOnly, td.[Pay To] AS PaidTo, NULL AS PaidBy, -- unable to find td.[Payee Name] AS Payee, NULL AS VendorId, -- SE SupplierID — add to expense views CASE WHEN td.[Grossed Up] = 'Grossed Up' THEN 1 ELSE 0 END AS GrossUpCategory, -- approximate NULL AS WithholdingCategory, -- unable to find (coarse: Taxability available) NULL AS CategoryHomeSaleCosts, -- unable to find NULL AS ItemHomeSaleCosts, -- unable to find pf.[Domestic or International] AS DomesticIntl FROM se_reports.dbo.[udv_transaction_detail_34] td JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON td.[File #] = pf.[File #] LEFT JOIN se_reports.dbo.[udv_Expense_Reports_34] er ON td.[Expense Report ID] = er.[Expense Report ID] 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 SentToPayroll ...) — unable to find ) 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], td.[Payables PO Line Number];