cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Business One Customer Account Balance Query

Former Member
0 Kudos

Hi All,

how can i query a customer account receivable balance,

example:

Customer A have a balance of 5000 as of 28/08/2016, now he/she have invoice dated 29/08/2016 amount of 2000 and the invoice is already process and debited to customer as AR, now when i generate the query dated to 29/08/2016, the display balance of Customer A  is 7000 and also in payment example in the next day 30/08/2016 his payment arrive and process in incoming payment so the balance of customer A has credited amount of 2000 or the invoice amount, so when i generate query the balance of customer A is back to 5000. can any one help me regarding with my concern,

Thanks a lot guys,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Dear Erwin Eder,

Please check the below query for your requirement,

SELECT T2.CardCode, T2.CardName, SUM(T1.Debit), SUM(T1.Credit), (SUM(T1.Debit)- SUM(T1.Credit)) AS Balance

FROM OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN  OCRD T2 ON T1.ShortName=T2.CardCode

WHERE T0.RefDate <=[%0] AND  T2.CardType =[%1]

GROUP BY T2.CardCode, T2.CardName

Regards

Karunakaran P.

Answers (2)

Answers (2)

K_Pauquet
Advisor
Advisor
0 Kudos

Hi Erwin Eder,

you can use the ageing report to set to a particular ageing date, such that transactions after that date are not taken into consideration. You can also select to display reconciled transactions & to ignore future remits.

All the best,

Kerstin

former_member237214
Participant
0 Kudos

Hi,

In OCRD Table you can use Account field, It will give you current balance when ever you execute a query.. 

Thanks..

Former Member
0 Kudos

Hi Sir Logesan S,

it is possible to filter date the custmer account balance base on my example below,

Thanks,