/* Open Receivables Aging — with service provider (DatawarehouseNCM) Crystal: OpenReceivablesAgingWithSP.rpt BO seeded tblTERptTempFile from vwTEFullServiceList; DW filters TEFileNo via DimTEFullServiceList.SrvcProvider (no user-initials temp table). */ DECLARE @CorpCode varchar(15) = NULL; DECLARE @SrvcProvider varchar(4) = 'XXXX'; -- DimTEFullServiceList.SrvcProvider / DimConsultant.Initials SELECT f.CorpCode, c.FormalName, f.ParentCompany, f.TEFileNo, f.TELastName, f.TEFirstName, f.InvoiceNo, f.InvoiceDate, f.DueDate, f.OpenAmount, 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 EXISTS ( SELECT 1 FROM dbo.DimTEFullServiceList s WHERE s.TEFileNo = f.TEFileNo AND s.SrvcProvider = @SrvcProvider ) AND (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) ORDER BY f.CorpCode, f.TEFileNo, f.DueDate, f.InvoiceNo;