/* Exception Sample Report — detail rows (DatawarehouseNCM) BackOffice: frmRptException / Crystal ExceptionAdjustmentRpt.rpt Source fact: dbo.FactExceptionManagement (loaded by PrcPopulateDatawarehouseTables) Crystal base WHERE (applied at Fact load): IsAdjustment = 0 AND WorkflowStateID IN (35, 38, 48) CreatedOn between start/end (filter below); CorpCode filter below ExceptionStatus = Crystal @StatusOrder: 0 Processed — StateID=38, not ItemDenied, and ((TrackInERP=1 and Amount<>0) or TrackInERP=0) 1 Approved — StateID=38, not ItemDenied, TrackInERP=1 and Amount is 0/null 2 Pending — other states (not in Crystal base set) 3 Denied — StateID=38 and ItemDenied=1, or StateID in (35, 48) */ -- DECLARE @CorpCode VARCHAR(4) = NULL; -- DECLARE @StartDate DATETIME = '2026-06-01'; -- DECLARE @EndDate DATETIME = '2026-06-29'; -- 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, f.ExceptionDate AS [Date], -- Crystal Date formula f.CreatedOn, -- Crystal start/end filter f.EstimatedAmount, ISNULL(f.ProcessedAmount, 0.00) AS ProcessedAmount, f.ReasonDesc AS Reason, f.CostImpactDesc AS CostImpact, CASE WHEN f.CostImpactDesc LIKE '%Increase%' THEN ISNULL(f.CostIncrease, 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, f.ExceptionStatus, f.DomesticIntl, f.EAWorkFlowID, f.WorkFlowControlID, f.WorkflowStateID 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 WHERE f.ExceptionStatus IS NOT NULL -- 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 f.ExceptionStatus WHEN 'Processed' THEN 0 WHEN 'Approved' THEN 1 WHEN 'Pending' THEN 2 WHEN 'Denied' THEN 3 ELSE 4 END, dm.TEName, f.ExceptionDate;