cancel
Showing results for 
Search instead for 
Did you mean: 

Finding Business Partner balance as at a specified date

Former Member
0 Kudos

Hi,

Can anyone offer any help on how I can calculate the business partner balance as at any given date?

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

former_member201110
Active Contributor
0 Kudos

Hi Greg,

This should work, just paste it into the Query Generator

SELECT SUM(T0.Debit - T0.Credit) AS Balance from JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode where T0.RefDate <= '[%0]' AND T0.TransType in (13, 14, 24, 30, 132, 18, 19, 69, 163, 46, 57) AND T1.CardCode = '[%1]'

Hope this helps,

Owen

Former Member
0 Kudos

Thanks Owen, why not allow all transactions types?

former_member201110
Active Contributor
0 Kudos

Hi Greg,

Because the join between the JDT1 and OCRD tables uses the ShortName column from JDT1 (ie not a column specifically for Business Partner code), it's conceivably possible that you'd get incorrect results returned by the query if your naming convention for business partner codes matches those of other master data records. Therefore I prefer to filter out any transaction types that won't directly affect a business partner's balance, just to be safe

Regards,

Owen

Answers (0)