/* Open Receivables Aging — Full Client Detail Crystal: OpenReceivablesAging.rpt (Pm-Output = 0) BO: frmOpenReceivable Reconstructed (Crystal SQL not extractable from .rpt): Open AR in RM20101 (CURTRXAM <> 0, VOIDSTTS = 0) TE via SOP + mdSOPLineWORK.GROUPID Aging from DueDate vs @AsOfDate Profit center / CAC / initials omitted (DW pattern). Run on RAC. */ DECLARE @AsOfDate datetime = CAST(GETDATE() AS date); DECLARE @CorpCode varchar(15) = NULL; -- NULL / '' = All DECLARE @ParentRollup bit = 0; -- 1 = filter/group by ParentCompany DECLARE @ParentCompany varchar(15) = NULL; -- used when @ParentRollup = 1 ;WITH InvoiceTE AS ( SELECT RTRIM(rm.CUSTNMBR) AS CorpCode, RTRIM(rm.DOCNUMBR) AS InvoiceNo, rm.DOCDATE AS InvoiceDate, rm.DUEDATE AS DueDate, rm.RMDTYPAL AS DocType, CASE WHEN rm.RMDTYPAL = 7 THEN -rm.ORTRXAMT ELSE rm.ORTRXAMT END AS OriginalAmount, CASE WHEN rm.RMDTYPAL = 7 THEN -rm.CURTRXAM ELSE rm.CURTRXAM END AS OpenAmount, RTRIM(MAX(w.GROUPID)) AS TEFileNo FROM dbo.RM20101 rm LEFT JOIN dbo.SOP30200 h ON rm.DOCNUMBR = h.SOPNUMBE LEFT JOIN dbo.SOP30300 l ON h.SOPNUMBE = l.SOPNUMBE AND h.SOPTYPE = l.SOPTYPE LEFT JOIN dbo.mdSOPLineWORK w ON l.SOPNUMBE = w.SOPNUMBE AND l.SOPTYPE = w.SOPTYPE AND l.LNITMSEQ = w.LNITMSEQ AND l.CMPNTSEQ = w.CMPNTSEQ WHERE rm.CURTRXAM <> 0 AND rm.VOIDSTTS = 0 AND rm.RMDTYPAL IN (1, 7) -- invoice, credit memo GROUP BY rm.CUSTNMBR, rm.DOCNUMBR, rm.DOCDATE, rm.DUEDATE, rm.RMDTYPAL, rm.ORTRXAMT, rm.CURTRXAM ) SELECT i.CorpCode, cl.FormalName, cl.ParentCompany, i.TEFileNo, m.TELastName, m.TEFirstName, i.InvoiceNo, i.InvoiceDate, i.DueDate, i.DocType, i.OriginalAmount, i.OpenAmount, DATEDIFF(day, i.DueDate, @AsOfDate) AS DaysPastDue, CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) <= 0 THEN 'Current' WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 1 AND 30 THEN '1-30' WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 31 AND 60 THEN '31-60' WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 61 AND 90 THEN '61-90' ELSE 'Over 90' END AS AgingBucket, CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) <= 0 THEN i.OpenAmount ELSE 0 END AS CurrentAmt, CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 1 AND 30 THEN i.OpenAmount ELSE 0 END AS Days1To30Amt, CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 31 AND 60 THEN i.OpenAmount ELSE 0 END AS Days31To60Amt, CASE WHEN DATEDIFF(day, i.DueDate, @AsOfDate) BETWEEN 61 AND 90 THEN i.OpenAmount ELSE 0 END AS Days61To90Amt, 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 LEFT JOIN dbo.tblTEMain1 m ON i.TEFileNo = m.TEFileNo 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) ORDER BY CASE WHEN @ParentRollup = 1 THEN cl.ParentCompany ELSE i.CorpCode END, i.CorpCode, i.TEFileNo, i.DueDate, i.InvoiceNo;