/* Exception Sample Report — detail at expense ITEM grain (Crystal-style) One row per FactRelocationExpense line linked by TEExceptionID (ExceptionLine=1, DeniedExpense=0), matching Crystal's vwREMExceptions expansion. EAs with no expense lines still return one row (ProcessedAmount NULL). Status and Date are recalculated per expense line (Crystal uses that line's Amount / TransactionDate). Filter start/end on CreatedOn (Crystal WHERE). Requires FactExceptionManagement columns from latest PrcPopulateDatawarehouseTables (TEExceptionID, TrackInERP, CreatedOn, date component columns). */ -- DECLARE @CorpCode VARCHAR(4) = NULL; -- DECLARE @StartDate DATETIME = '2025-07-15'; -- DECLARE @EndDate DATETIME = '2025-12-15'; -- DECLARE @DomesticIntl INT = 1; -- DECLARE @ReloPolicy VARCHAR(100) = NULL; SELECT dm.TEName AS EmployeeName, dm.TEFileNo AS FileNo, dm.CorpCode, dm.FormalName, div.DivisionName AS Division, pol.ReloPolicy AS Policy, f.Category, f.Item, -- Crystal Date formula using this expense line's TransactionDate CASE WHEN f.SentToClientDate IS NOT NULL THEN CASE WHEN f.ItemDenied = 1 THEN CASE WHEN f.ClientDeniedDate IS NOT NULL THEN f.ClientDeniedDate ELSE f.DeniedDate END ELSE CASE WHEN ed.TransactionDate IS NOT NULL THEN ed.TransactionDate ELSE f.ClientApprovedDate END END ELSE CASE WHEN f.ItemDenied = 1 THEN CASE WHEN f.DeniedDate IS NOT NULL THEN f.DeniedDate WHEN ed.TransactionDate IS NOT NULL THEN ed.TransactionDate ELSE f.DateOpsProcessed END ELSE CASE WHEN ed.TransactionDate IS NOT NULL THEN ed.TransactionDate WHEN f.ApprovalDate IS NOT NULL THEN f.ApprovalDate ELSE f.DateOpsProcessed END END END AS [Date], f.CreatedOn, f.EstimatedAmount, ed.Amount AS ProcessedAmount, f.ReasonDesc AS Reason, f.CostImpactDesc AS CostImpact, CASE WHEN f.CostImpactDesc LIKE '%Increase%' THEN ISNULL(ed.Amount, 0.00) ELSE 0.00 END AS CostIncrease, CASE WHEN f.CostImpactDesc LIKE '%Savings%' THEN ISNULL(f.SavingsAmount, 0.00) ELSE 0.00 END AS SavingsAmount, f.NCMComments, f.ApprovedBy AS ApprovedDeniedBy, f.ClientDeniedComments AS ClientComments, -- Crystal StatusOrder using this line's Amount CASE WHEN f.WorkflowStateID = 38 THEN CASE WHEN f.ItemDenied = 1 THEN 'Denied' WHEN (f.TrackInERP = 1 AND ed.Amount IS NOT NULL AND ed.Amount <> 0) OR f.TrackInERP = 0 THEN 'Processed' ELSE 'Approved' END WHEN f.WorkflowStateID IN (35, 48) THEN 'Denied' ELSE 'Pending' END AS ExceptionStatus, f.DomesticIntl, f.EAWorkFlowID, f.WorkFlowControlID, f.TEExceptionID, f.WorkflowStateID, ed.ExpDetailID, ed.TransactionDate AS ExpenseTransactionDate FROM dbo.FactExceptionManagement f INNER JOIN dbo.DimMain dm ON dm.DimMainID = f.DimMainID LEFT JOIN dbo.DimDivision div ON div.DimDivisionID = f.DimDivisionID LEFT JOIN dbo.DimPolicy pol ON pol.DimPolicyID = f.DimPolicyID LEFT JOIN dbo.FactRelocationExpense ed ON ed.TEExceptionID = f.TEExceptionID AND f.TEExceptionID <> 0 AND ed.ExceptionLine = 1 AND ed.DeniedExpense = 0 WHERE 1 = 1 -- AND (@CorpCode IS NULL OR dm.CorpCode = @CorpCode) -- AND (@StartDate IS NULL OR f.CreatedOn >= @StartDate) -- AND (@EndDate IS NULL OR f.CreatedOn < DATEADD(DAY, 1, @EndDate)) -- AND (@DomesticIntl IS NULL OR f.DomesticIntl = @DomesticIntl) -- AND (@ReloPolicy IS NULL OR @ReloPolicy = '' OR pol.ReloPolicy = @ReloPolicy) ORDER BY CASE WHEN f.WorkflowStateID = 38 THEN CASE WHEN f.ItemDenied = 1 THEN 3 WHEN (f.TrackInERP = 1 AND ed.Amount IS NOT NULL AND ed.Amount <> 0) OR f.TrackInERP = 0 THEN 0 ELSE 1 END WHEN f.WorkflowStateID IN (35, 48) THEN 3 ELSE 2 END, dm.TEName, f.CreatedOn, ed.ExpDetailID;