/* Open Receivables Aging — Client Summary Crystal: OpenReceivablesAgingClientSummary.rpt (Pm-Output = 1) BO: frmOpenReceivable Same open-AR + aging reconstruction as 01 Detail; aggregated by client (or parent company when @ParentRollup = 1). Run on RAC. */ DECLARE @AsOfDate datetime = CAST(GETDATE() AS date); DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @ParentCompany varchar(15) = NULL; ;WITH InvoiceTE AS ( SELECT RTRIM(rm.CUSTNMBR) AS CorpCode, CASE WHEN rm.RMDTYPAL = 7 THEN -rm.CURTRXAM ELSE rm.CURTRXAM END AS OpenAmount, rm.DUEDATE AS DueDate FROM dbo.RM20101 rm WHERE rm.CURTRXAM <> 0 AND rm.VOIDSTTS = 0 AND rm.RMDTYPAL IN (1, 7) ) SELECT CASE WHEN @ParentRollup = 1 THEN cl.ParentCompany ELSE i.CorpCode END AS ClientKey, MAX(CASE WHEN @ParentRollup = 1 THEN cl.ParentCompany ELSE cl.FormalName END) AS ClientName, CASE WHEN @ParentRollup = 1 THEN MAX(cl.ParentCompany) ELSE MAX(cl.ParentCompany) END AS ParentCompany, COUNT(*) AS OpenInvoiceCount, SUM(i.OpenAmount) AS OpenAmount, SUM(CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) <= 0 THEN i.OpenAmount ELSE 0 END) AS CurrentAmt, SUM(CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 1 AND 30 THEN i.OpenAmount ELSE 0 END) AS Days1To30Amt, SUM(CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 31 AND 60 THEN i.OpenAmount ELSE 0 END) AS Days31To60Amt, SUM(CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 61 AND 90 THEN i.OpenAmount ELSE 0 END) AS Days61To90Amt, SUM(CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) > 90 THEN i.OpenAmount ELSE 0 END) AS DaysOver90Amt FROM InvoiceTE i LEFT JOIN dbo.tblCorpClient cl ON i.CorpCode = cl.CorpCode WHERE (@CorpCode IS NULL OR @CorpCode = '' OR i.CorpCode = @CorpCode OR (@ParentRollup = 1 AND cl.ParentCompany = @CorpCode)) AND (@ParentRollup = 0 OR @ParentCompany IS NULL OR @ParentCompany = '' OR cl.ParentCompany = @ParentCompany) GROUP BY CASE WHEN @ParentRollup = 1 THEN cl.ParentCompany ELSE i.CorpCode END ORDER BY ClientKey;