----01 Relocation Expense Detail.sql -- DateType: T = TransactionDate, P = SentToPayroll, M = MoveComplete -- Defaults mirror typical BO run: exclude denied, exclude track-only, exclude ExcludeFromStdReports. -- 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 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, NULL AS DisplayType, -- Not used in report td.[Payables PO Line Number] AS ExpDetailID, td.[Expense Report ID] AS ExpenseSheetID, td.[Transaction Date] AS TransactionDate, td.[Payroll Batch Date] AS SentToPayroll, er.[Submitted Date] AS SubmittedForPmt, NULL AS CategoryID, -- Not used in report td.[Expense Category] AS Category, NULL AS ItemID, -- Not used in report td.[Expense Item] AS Item, td.[Transaction Amount] AS Amount, er.[Report From Date] AS PeriodStart, er.[Report To Date] AS PeriodEnd, td.[Comments] AS Comment, td.[Exception] AS ExceptionLine, td.[Exception ID] AS ExceptionAppliesTo, NULL AS DeniedExpense, -- Not reporting Denied in SE CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END AS TrackOnly, td.[Pay To] AS PaidTo, td.[Payment Type] AS PaidBy, td.[Payee Name] AS Payee, CASE WHEN td.[Pay To] = 'Organization' THEN td.[Payee Name] ELSE NULL END AS Vendor, CASE WHEN td.[Grossed Up] = 'Grossed Up' THEN 1 ELSE 0 END AS GrossUpCategory, NULL AS WithholdingCategory, -- Not used in report NULL AS CategoryHomeSaleCosts, -- Not used in report NULL AS ItemHomeSaleCosts, -- Not used in report 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 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], td.[Payables PO Line Number];