/* Open Receivables Aging — detail (DatawarehouseNCM) Fact: dbo.FactOpenReceivable Crystal: OpenReceivablesAging.rpt (Full Client Detail) */ DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = All DECLARE @ParentRollup bit = 0; DECLARE @ParentCompany varchar(15) = NULL; SELECT f.CorpCode, c.FormalName, f.ParentCompany, f.TEFileNo, f.TELastName, f.TEFirstName, f.InvoiceNo, f.InvoiceDate, f.DueDate, f.DocType, f.OriginalAmount, f.OpenAmount, f.DaysPastDue, f.AgingBucket, f.CurrentAmt, f.Days1To30Amt, f.Days31To60Amt, f.Days61To90Amt, f.DaysOver90Amt, f.AsOfDate FROM dbo.FactOpenReceivable f LEFT JOIN dbo.DimCorpClient c ON f.CorpCode = c.CorpCode WHERE (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode OR (@ParentRollup = 1 AND f.ParentCompany = @CorpCode)) AND (@ParentRollup = 0 OR @ParentCompany IS NULL OR @ParentCompany = '' OR f.ParentCompany = @ParentCompany) ORDER BY CASE WHEN @ParentRollup = 1 THEN f.ParentCompany ELSE f.CorpCode END, f.CorpCode, f.TEFileNo, f.DueDate, f.InvoiceNo;