--01 Expense Audit By Transaction Date.sql -- SE rewrite of BO Expense Processing Audit (FactExpenseProcessingAudit) -- Views: udv_transaction_detail_34, udv_profile_information_view_34, udv_Expense_Reports_34 -- @ParentRollup / ParentCompany — unable to find -- 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 @TEFileNo varchar(50) = NULL; 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.[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, td.[Payables PO Line Number] AS ExpDetailID, td.[Expense Report ID] AS ExpenseSheetID, td.[Transaction Date] AS TransactionDate, er.[Submitted Date] AS SubmittedForPmt, -- This is included in udv_Expense_Reports_34 - can it be added to udv_transaction_detail_34 NULL AS SentToPayroll, -- unable to find td.[Due Date] AS DueDate, td.[Invoice Date] AS DocDate, -- unable to find -- closest; BO DocDate er.[Processed Date] AS AcctgConfirmedDate, -- unable to find -- approximate -- This is included in udv_Expense_Reports_34 - can it be added to udv_transaction_detail_34 td.[Expense Category] AS Category, td.[Expense Item] AS Item, NULL AS OrigCategory, -- NOT USED IN SE td.[Transaction Amount] AS Amount, er.[Incurred Amount] AS IncurredAmount, -- This is included in udv_Expense_Reports_34 - can it be added to udv_transaction_detail_34 td.[Paid Amount] AS PaidAmount, td.[Payee Name] AS Payee, NULL AS VendorId, -- SE SupplierID — add to expense views td.[Pay To] AS PaidTo, NULL AS PaidBy, -- unable to find td.[Payment Type] AS PaymentMethod, td.[Payables PO Number] AS DocNumber, -- approximate td.[Invoice Number] AS InvoiceNumber, 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, -- Yes/No vs BO flag NULL AS DenialReason, -- unable to find td.[Comments] AS Comment, td.[Service] AS ServiceCode, -- description, not code NEED Workflow Name also? NULL AS ServiceType -- unable to find 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 td.[Transaction 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 (@TEFileNo IS NULL OR @TEFileNo = '' OR td.[File #] = @TEFileNo) ORDER BY td.[Corp Code], td.[Last Name], td.[First Name], td.[File #], td.[Transaction Date], td.[Payables PO Line Number];