cancel
Showing results for 
Search instead for 
Did you mean: 

Customer Aging Report query

Former Member
0 Kudos

Kindly help me with a query to generate a customer aging covering;

1. Invoices

2. Credit Memos

3. Payments

4. Manual Journal Entries

I have tried one of the aging queries posted in the forum, but the results are incorrect for some customers.

Thanks in advance,

Avinash

Accepted Solutions (0)

Answers (5)

Answers (5)

0 Kudos

Hi,

I want to run this query to get the ageing report from multiple company which are connected by Intercompany Addon.

Suppose i have two companies db1 and db2.

select OCRD.cardcode 'Customer Code',OCRD.cardname 'Customer Name',

SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) "Balance Due",


ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) < 0
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "Future Remit",

ISNULL(SUM(CASE when (DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 0
and datediff(dd,JDT1.DueDate,current_timestamp)< 30)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "0-30 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 30
and datediff(dd,JDT1.DueDate,current_timestamp)< 60)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "31 to 60 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 60
and datediff(dd,JDT1.DueDate,current_timestamp)< 90)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "61 to 90 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 90
and datediff(dd,JDT1.DueDate,current_timestamp) < 120)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "91 to 120 days",

ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 120
then 
	case
		when BalDueCred <> 0 then BalDueCred * - 1
		else BalDueDeb
	end
end),0.00) "120+ days"

from JDT1,OCRD 
WHERE JDT1.shortname = OCRD.cardcode and OCRD.cardtype = 'c' 
GROUP BY OCRD.cardcode, OCRD.cardname
--Filters Out Zero Balances
HAVING SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) > 0 OR SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) < 0

ORDER BY OCRD.CARDCODE
nitmanet168
Explorer
0 Kudos

select t0.CardCode,t0.CardName ,t0.DocDate,t0.DocDueDate,t0.doctotal AS 'Amount',t0.PaidToDate As Paid,(t0.DocTotal-t0.PaidToDate) AS 'Balance '

,ISNULL((SELECT t0.DocTotal-t0.PaidToDate WHERE DateDiff(day, T0.DocDueDate,'[%1]')<=-1),0) 'Future'

,ISNULL((SELECT t0.DocTotal-t0.PaidToDate WHERE DateDiff(day, T0.DocDueDate,'[%1]')>=0 and DATEDIFF(DAY,T0.DocDueDate,'[%1]')<=30),0) 'Current'

,ISNULL((select t0.DocTotal-t0.PaidToDate where datediff(DAY,T0.DocDueDate,'[%1]')>30 and DATEDIFF(day,T0.DocDueDate,'[%1]')<=60),0) '31-60 Days'

,ISNULL((select t0.DocTotal-t0.PaidToDate where datediff(DAY,T0.DocDueDate,'[%1]')>60 and DATEDIFF(day,T0.DocDueDate,'[%1]')<=90),0) '61-90 Days'

,ISNULL((select t0.DocTotal-t0.PaidToDate where datediff(DAY,T0.DocDueDate,'[%1]')>90 and DATEDIFF(day,T0.DocDueDate,'[%1]')<=120),0) '91-120 Days'

,ISNULL((SELECT t0.DocTotal-t0.PaidToDate WHERE DateDiff(day, T0.DocDueDate,'[%1]')>=121),0) '121+ Days'

from oinv T0

where (DocTotal-PaidToDate) <>0 and t0.DocStatus='O' and t0.CANCELED='N' and t0.DocDate<=[%1]

ORDER BY t0.CardName

0 Kudos

Give this a try.

Ken Worst

--
-- SAP B1 Customer Receivables Aging - By Due Date
-- 
--
--
-- Version 10022008
-- 



select OCRD.cardcode 'Customer Code',OCRD.cardname 'Customer Name',

SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) "Balance Due",


ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) < 0
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "Future Remit",

ISNULL(SUM(CASE when (DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 0
and datediff(dd,JDT1.DueDate,current_timestamp)< 30)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "0-30 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 30
and datediff(dd,JDT1.DueDate,current_timestamp)< 60)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "31 to 60 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 60
and datediff(dd,JDT1.DueDate,current_timestamp)< 90)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "61 to 90 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 90
and datediff(dd,JDT1.DueDate,current_timestamp) < 120)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "91 to 120 days",

ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 120
then 
	case
		when BalDueCred <> 0 then BalDueCred * - 1
		else BalDueDeb
	end
end),0.00) "120+ days"

from JDT1,OCRD 
WHERE JDT1.shortname = OCRD.cardcode and OCRD.cardtype = 'c' 
GROUP BY OCRD.cardcode, OCRD.cardname
--Filters Out Zero Balances
HAVING SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) > 0 OR SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) < 0

ORDER BY OCRD.CARDCODE

Former Member
0 Kudos

Hi Ken,

I tried to use the query which you have posted, however its not fetching any data at all. Any ideas why is this happening? SQL doesnt give any error message.

Thanks,

Joseph

0 Kudos

Joseph,

Try commenting out the following and see if you get any rows

--Filters Out Zero Balances
--HAVING SUM(case when syscred <> 0 then syscred * - 1
--else sysdeb
--end) > 0 OR SUM(case when syscred <> 0 then syscred * - 1
--else sysdeb
--end) < 0

This should show all customer records no matter the balance.

I have tested this only on 2007A on MSSQL 2005 only.

Hope this helps.

Ken Worst

Former Member
0 Kudos

Please guide me on how to bring in Project Code based aging report in the Customer Aging Query.

Former Member
0 Kudos

I have used the query by Ken and modified the fields for SBO 2005, which can provide me the total pending.

But some of the fields like BalDueCred and BalDueDeb in JDT1 seems to be associated with 2007 version are not present in 2005.

Kindly specify the fields which can be used in lieu of these.

thanks,

Avinash

Former Member
0 Kudos

The logic from that query has some problems. It is not easy to understand the originators intention though. The misery is: it was marked problem solved. I don't think it can bring the right result. Try correct it by yourself first. If you are unable to modify it, post your question here.

Former Member
0 Kudos

Post the query you tried here so that we can start troubleshooting.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

I have used the query from the post:

Pls direct me if there is any other post which details an aging query.

thanks,

Avinash