SQL to generate AR and AP balances at a given date

[Former Member]

We are using a SAP Business One System running on a SQL Server 14 database.

I am trying to calculate the AR and AP at a given date in the past using SQL.

The way our CFO explained it to me when an invoice is created the Revenue account is credited in the GL and the Account Receivable account is debited. When we receive payment we credit the Account Receivable account and debit cash.

I have tried using the General Ledger but although the Accounts Receivable account (1100-00) is debited when we create the invoice it is not credited when we receive payment. Does anyone know how SAP handles AR and AP and what I need to do to generate the AR and AP?

I am looking at the OJDT and JDT1 tables

Thanks

SD

[former_member588507]

Hi Stephan,

Please check Journal Entries when you adding incoming payment, in JE the control account should be same debtors account as you mentioned above. If there any difference then first check BP master's accounting tab and make sure there should be same account entered in Accounts Receivable.

Let me know your issue resolved or not?

Regards,

Satish Kumar

[former_member419594]

Hi,

you can try with that query:

Select 
T2.[AcctCode], T2.[AcctName], SUM(ISNUll(T1.Debit,0)-ISNUll(T1.Credit,0))
From OJDT T0
Inner Join JDT1 T1 on T0.TransId = T1.TransId
Inner Join OACT T2 on T1.Account = T2.AcctCode
Where T0.RefDate < '[%1]'
Group By T2.[AcctCode], T2.[AcctName]

'[%1]' for using in SAP B1 to enter a date... in SQL you have to fill in the given date. (for example: < '2018-11-01')

Best regards

Markus