Query for Accounts Receivable Past Due

[pronet74]

I am on ES and back when we were on progression there were fields that showed how much was 30, 60 and 90 days overdue. Now ES calculates this on the fly by using a very complex set of queries.

I was wondering if any of you have anything that would accomplish this. I tried running the sql profiler then running an aging analysis report but it's just way too complex (ie. tables are created, etc..).

Not sure the best way to go about this, any help would be appreciated.

 

[crystalreporting]

Here's a quick query that I believe will give you your aging buckets along with basic information required to produce an AR Aging report. Try this in Query Analyzer first, then create it as a view if you find it works OK;

SELECT DebtorNumber AS Debtor_Number, ci.debcode AS Cus_Number, ci.cmp_name AS Cus_Name, SUM(ROUND((CASE WHEN bt.AmountDC > 0 AND
bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN bt.Type = 'S' AND bt.AmountDC < 0 THEN - bt.AmountDC ELSE NULL END) END), 2)) AS Debit,
SUM(ROUND((CASE WHEN bt.AmountDC < 0 AND bt.Type = 'W' THEN - bt.AmountDC ELSE (CASE WHEN bt.Type = 'S' AND
bt.AmountDC > 0 THEN bt.AmountDC ELSE NULL END) END), 2)) AS Credit, SUM(ROUND((CASE WHEN DATEDIFF(dd, ISNULL(bt.InvoiceDate,
bt.ProcessingDate), GetDate()) < 31 AND bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, GetDate()) < 31 AND
bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2)) AS T1, SUM(ROUND((CASE WHEN DATEDIFF(dd, ISNULL(bt.InvoiceDate,
bt.ProcessingDate), GetDate()) BETWEEN 31 AND 60 AND bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, GetDate())
BETWEEN 31 AND 60 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2)) AS T2, SUM(ROUND((CASE WHEN DATEDIFF(dd,
ISNULL(bt.InvoiceDate, bt.ProcessingDate), GetDate()) BETWEEN 61 AND 90 AND bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd,
bt.ValueDate, GetDate()) BETWEEN 61 AND 90 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2)) AS T3,
SUM(ROUND((CASE WHEN DATEDIFF(dd, ISNULL(bt.InvoiceDate, bt.ProcessingDate), GetDate()) > 90 AND
bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, GetDate()) > 90 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL
END) END), 2)) AS T4, SUM(ROUND((CASE WHEN bt.Type = 'W' THEN bt.AmountDC ELSE - bt.AmountDC END), 2)) AS Total, COUNT(*) AS Term_Code,
AVG(DATEDIFF(dd, bt.InvoiceDate, GetDate())) AS Age, MAX(addr.AddressLine1) AS Address, MAX(addr.City) AS City, MAX(addr.Postcode) AS Zip,
MAX(addr.StateCode) AS State, ci.cmp_fctry AS Country, MAX(addr.Phone) AS Phone, MAX(addr.Fax) AS Fax
FROM ((SELECT '' AS Empty, bt.ID, DebtorNumber, CreditorNumber, ValueDate, AmountDC, AmountTC, ProcessingDate, InvoiceDate, Type, OffSetName,
PaymentType, SupplierInvoiceNumber, CAST(Description AS VARCHAR(400)) AS Description, TransactionType, OffsetReference,
OffSetLedgerAccountNumber, bt.Blocked, DocumentID, OrderNumber, InvoiceNumber, DueDate, TcCode, bt.Status, MatchID, BatchNumber,
OwnBankAccount, EntryNumber
FROM BankTransactions bt INNER JOIN
cicmpy ci ON bt.DebtorNumber = ci.debnr
WHERE Type = 'W' AND bt.Status IN ('C', 'A', 'P', 'J') AND EntryNumber IS NOT NULL AND MatchID IS NULL AND DebtorNumber IS NOT NULL AND
ROUND(AmountDC, 2) <> 0 AND (ci.cmp_type = 'C')
UNION ALL
SELECT '' AS Empty, s.ID, s.DebtorNumber, s.CreditorNumber, s.ValueDate, (IsNull(s.AmountDC, 0) - IsNull(W2.AmountDC, 0)) AS AmountDC,
(IsNull(s.AmountTC, 0) - IsNull(W2.AmountTC, 0)) AS AmountTC, s.ProcessingDate, s.InvoiceDate, s.Type, s.OffSetName, s.PaymentType,
s.SupplierInvoiceNumber, CAST(s.Description AS VARCHAR(400)) AS Description, s.TransactionType, s.OffsetReference,
s.OffSetLedgerAccountNumber, s.Blocked, s.DocumentID, s.OrderNumber, ISNULL(s.InvoiceNumber,
(SELECT TOP 1 g.faktuurnr
FROM gbkmut g
WHERE g.BankTransactionGuid = s.sysguid)) AS InvoiceNumber, s.DueDate, s.TCCode, s.Status, s.MatchID, s.BatchNumber,
s.OwnBankAccount, s.EntryNumber
FROM BankTransactions s INNER JOIN
cicmpy ci ON s.DebtorNumber = ci.debnr LEFT OUTER JOIN
(SELECT MatchID, ROUND(SUM(ROUND(ISNULL(AmountDC, 0), 2)), 2) AS AmountDC, ROUND(SUM(ROUND(ISNULL(AmountTC, 0), 2)), 2)
AS AmountTC
FROM BankTransactions w
WHERE w.Type = 'W' AND w.Status IN ('C', 'A', 'P', 'J') AND w.EntryNumber IS NOT NULL
GROUP BY MatchID
HAVING MatchID IS NOT NULL) AS W2 ON W2.MatchID = S.ID
WHERE s.Type = 'S' AND s.Status <> 'V' AND s.DebtorNumber IS NOT NULL AND ROUND(s.AmountDC, 2) <> 0 AND (IsNull(s.AmountDC, 0)
- IsNull(W2.AmountDC, 0)) <> 0 AND (ci.cmp_type = 'C'))) bt INNER JOIN
cicmpy ci ON bt.DebtorNumber = ci.debnr AND bt.DebtorNumber IS NOT NULL INNER JOIN
addresses addr ON ci.cmp_wwn = addr.account AND addr.Main = 1 AND addr.Type = 'INV' LEFT OUTER JOIN
cicntp cp ON cp.cnt_id = ci.cnt_id
WHERE ci.debcode IS NOT NULL AND ci.cmp_type = 'C'
GROUP BY ci.debcode, bt.Debtornumber, ci.cmp_name, ci.cmp_type, ci.cmp_status, ci.cmp_fctry


Peter Shirley

http://www.srhconsulting.com

 

[pronet74]

Awesome!! Thank you very much! Works great!

 

[stravis]

For future readers please be aware that this query may not work for all installations of Macola ES. The reason being, is that Macola ES calculates the open AR for companies differently using multi-currency versus companies that work in US dollars only. From what I can see this query is for US dollars only.

 

[pronet74]

I have one problem with it though. It's not with the query itself but the reason why I wanted this was to let my customers know what they owe online. Having this put into a .asp page just bogs down the browser and no data appears.

So I was going to have it automatically create a table every night and let it query this new table, however creating a job with the enterprise manager it only allows me a 3200 character limit on my queries. This has over 4200 characters.

Would you happen to have any recommendations on how I can go about doing this?

 

[crystalreporting]

This query is taken from the balance list function in AR (AR/Reports/Balance List). It displays foreign currency customers in exactly the same way as the standard function does i.e. in the companies 'home' currency.

Peter Shirley

http://www.srhconsulting.com

 

[crystalreporting]

You could create a stored procedure to populate a table with the query results (using insert into ahead of your select) then schedule the stored procedure as a job.

Were you passing a customer number to the query via asp? I'm suprised it bogged the browser down at all.

Peter Shirley

http://www.srhconsulting.com

 

[pronet74]

What I did was create a vbs script and then created a job to run it early in the morning. If you want the script, let me know and I can post it on here.

Seems to be working great now