--02 Expense Audit By Authorization Date.sql -- SE rewrite of BO Expense Processing Audit (Auth Date mode) -- 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 @DomesticIntl varchar(50) = NULL; -- DECLARE @PrimaryContact varchar(100) = NULL; -- DECLARE @IncludeExpenses bit = 0; -- TE / descriptor list (Auth mode core) SELECT DISTINCT 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.[Employee ID] AS EmpNumber, pf.[Consultant] AS PrimaryContact, td.[Division] AS DivisionName, td.[Policy] AS ReloPolicy, td.[Move Type] AS MoveType, pf.[Domestic or International] AS DomesticIntl, NULL AS DisplayType, -- NOT USED IN SE pf.[Auth Date] AS AuthDate, td.[Client Descriptor 1] AS Descriptor1, td.[Client Descriptor 2] AS Descriptor2, td.[Client Descriptor 3] AS Descriptor3, td.[Client Descriptor 4] AS Descriptor4, td.[Client Descriptor 5] AS Descriptor5 FROM se_reports.dbo.[udv_transaction_detail_34] td JOIN se_reports.dbo.[udv_profile_information_view_34] pf ON td.[File #] = pf.[File #] WHERE 1 = 1 -- AND pf.[Auth Date] BETWEEN @StartDate AND @EndDate -- AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) -- AND (@DomesticIntl IS NULL OR @DomesticIntl = '' OR pf.[Domestic or International] = @DomesticIntl) -- AND (@PrimaryContact IS NULL OR @PrimaryContact = '' OR pf.[Consultant] = @PrimaryContact) -- AND @IncludeExpenses = 0 ORDER BY td.[Corp Code], td.[Last Name], td.[First Name], td.[File #]; -- Expense lines for TEs authorized in the window SELECT td.[Corp Code] AS CorpCode, td.[Org Name] AS FormalName, td.[File #] AS TEFileNo, td.[Last Name] AS TELastName, td.[First Name] AS TEFirstName, pf.[Auth Date] AS AuthDate, td.[Client Descriptor 1] AS Descriptor1, td.[Client Descriptor 2] AS Descriptor2, td.[Client Descriptor 3] AS Descriptor3, td.[Client Descriptor 4] AS Descriptor4, td.[Client Descriptor 5] AS Descriptor5, td.[Payables PO Line Number] AS ExpDetailID, td.[Transaction Date] AS TransactionDate, td.[Expense Category] AS Category, td.[Expense Item] AS Item, td.[Transaction Amount] AS Amount, td.[Payee Name] AS Payee, NULL AS DeniedExpense, -- unable to find CASE WHEN td.[Payment Type] = 'Client Already Paid' THEN 1 ELSE 0 END AS TrackOnly, td.[Exception] AS ExceptionLine 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 1 = 1 -- AND pf.[Auth Date] BETWEEN @StartDate AND @EndDate -- AND (@CorpCode IS NULL OR @CorpCode = '' OR td.[Corp Code] = @CorpCode) -- AND (@DomesticIntl IS NULL OR @DomesticIntl = '' OR pf.[Domestic or International] = @DomesticIntl) -- AND (@PrimaryContact IS NULL OR @PrimaryContact = '' OR pf.[Consultant] = @PrimaryContact) -- AND @IncludeExpenses = 1 ORDER BY td.[Corp Code], td.[File #], td.[Transaction Date], td.[Payables PO Line Number];