/* Expense Processing Audit — by authorization date (DatawarehouseNCM) Fact: dbo.FactExpenseProcessingAudit BackOffice: frmExpProcAuditRpt / Crystal: ExpenseProcessingAuditRpt.rpt Pm-TransType = A (Authorization Date) BO selects TEs from vwTEFullServiceList.AuthDate. DW fact stores DimMain.AuthDate (initial authorization). Use that for TE inclusion; optionally return expense lines for those TEs (any TransactionDate), matching Crystal's TE-scoped descriptor audit. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @DomesticIntl varchar(1) = NULL; DECLARE @PrimaryContact varchar(4) = NULL; DECLARE @IncludeExpenses bit = 0; -- 0 = TE/descriptor rollup; 1 = expense lines for authorized TEs -- TE / descriptor list (Auth mode core) SELECT DISTINCT f.CorpCode, f.FormalName, f.ParentCompany, f.TEFileNo, f.TELastName, f.TEFirstName, f.EmpNumber, f.PrimaryContact, f.DivisionName, f.ReloPolicy, f.MoveType, f.DomesticIntl, f.DisplayType, f.AuthDate, f.Descriptor1, f.Descriptor2, f.Descriptor3, f.Descriptor4, f.Descriptor5 FROM dbo.FactExpenseProcessingAudit f WHERE f.AuthDate BETWEEN @StartDate AND @EndDate AND ( (@ParentRollup = 0 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.ParentCompany = @CorpCode OR f.CorpCode = @CorpCode)) ) AND (@DomesticIntl IS NULL OR @DomesticIntl = '' OR f.DomesticIntl = @DomesticIntl) AND (@PrimaryContact IS NULL OR @PrimaryContact = '' OR f.PrimaryContact = @PrimaryContact) AND @IncludeExpenses = 0 ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.TEFileNo; -- Expense lines for TEs authorized in the window SELECT f.CorpCode, f.FormalName, f.TEFileNo, f.TELastName, f.TEFirstName, f.AuthDate, f.Descriptor1, f.Descriptor2, f.Descriptor3, f.Descriptor4, f.Descriptor5, f.ExpDetailID, f.TransactionDate, f.Category, f.Item, f.Amount, f.Payee, f.DeniedExpense, f.TrackOnly, f.ExceptionLine FROM dbo.FactExpenseProcessingAudit f WHERE f.AuthDate BETWEEN @StartDate AND @EndDate AND ( (@ParentRollup = 0 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode IS NULL OR @CorpCode = '' OR f.ParentCompany = @CorpCode OR f.CorpCode = @CorpCode)) ) AND (@DomesticIntl IS NULL OR @DomesticIntl = '' OR f.DomesticIntl = @DomesticIntl) AND (@PrimaryContact IS NULL OR @PrimaryContact = '' OR f.PrimaryContact = @PrimaryContact) AND @IncludeExpenses = 1 ORDER BY f.CorpCode, f.TEFileNo, f.TransactionDate, f.ExpDetailID;