/* Open Receivables Aging — With Service Provider Crystal: OpenReceivablesAgingWithSP.rpt BO: frmOpenReceivable — seeds RAC.dbo.tblTERptTempFile from ReloAction vwTEFullServiceList (TEFileNo / ServiceCode for selected SrvcProvider) This sample skips the initials temp table and filters TE files directly. Requires ReloAction access (linked server name may differ by environment). Run context: RAC + ReloAction. */ DECLARE @AsOfDate datetime = CAST(GETDATE() AS date); DECLARE @CorpCode varchar(15) = NULL; DECLARE @SrvcProvider varchar(4) = 'XXXX'; -- consultant initials (BO cboSrvcProvider.Tag) -- Prefer local synonym / same-server ReloAction; else use linked server: -- FROM [LNKRELOSQLDWH].[ReloAction].dbo.vwTEFullServiceList ;WITH SPFiles AS ( SELECT DISTINCT RTRIM(TEFileNo) AS TEFileNo FROM ReloAction.dbo.vwTEFullServiceList WHERE SrvcProvider = @SrvcProvider AND (@CorpCode IS NULL OR @CorpCode = '' OR CorpCode = @CorpCode) ), 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 INNER JOIN dbo.SOP30200 h ON rm.DOCNUMBR = h.SOPNUMBE INNER JOIN dbo.SOP30300 l ON h.SOPNUMBE = l.SOPNUMBE AND h.SOPTYPE = l.SOPTYPE INNER JOIN dbo.mdSOPLineWORK w ON l.SOPNUMBE = w.SOPNUMBE AND l.SOPTYPE = w.SOPTYPE AND l.LNITMSEQ = w.LNITMSEQ AND l.CMPNTSEQ = w.CMPNTSEQ INNER JOIN SPFiles sp ON RTRIM(w.GROUPID) = sp.TEFileNo WHERE rm.CURTRXAM <> 0 AND rm.VOIDSTTS = 0 AND rm.RMDTYPAL IN (1, 7) 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 ORDER BY i.CorpCode, i.TEFileNo, i.DueDate, i.InvoiceNo;