cancel
Showing results for 
Search instead for 
Did you mean: 

Querry

Former Member
0 Kudos
37

Hi

can you help with a querry to pull a list of sales accounts with their balances in foreign and local currencies at the end of a period, eg a month

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

jitin_chawla
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

Check this if it helps :

select SUM(t2.Debit)-SUM(t2.credit), t0.CurrTotal, t1.DebPayAcct,

t0.acctcode, t2.FCCurrency

from OACT t0

inner join JDT1 t2 on t0.acctcode = t2.account

inner join OCRD t1 on t1.cardcode = t2.shortname

where t1.CardType= 'C'

and t2.RefDate >='[%1]' and t2.RefDate<='[%2]'

group by t1.DebPayAcct,t0.acctcode,t2.FCCurrency, t0.CurrTotal

Kind Regards,

Jitin

SAP Business One Forum Team

Former Member
0 Kudos

the account name is not showing. i need the accounts name to show as well

jitin_chawla
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

Check this :

select SUM(t2.Debit)-SUM(t2.credit), t0.CurrTotal, t1.DebPayAcct,

t0.acctname, t0.acctcode, t2.FCCurrency

from OACT t0

inner join JDT1 t2 on t0.acctcode = t2.account

inner join OCRD t1 on t1.cardcode = t2.shortname

where t1.CardType= 'C'

and t2.RefDate >='[%1]' and t2.RefDate<='[%2]'

group by t1.DebPayAcct,t0.acctname, t0.acctcode,t2.FCCurrency, t0.CurrTotal

Kind Regards,

Jitin

SAP Business One Forum Team

Former Member
0 Kudos

It is showing the control accounts, but i am looking for the sales accounts in the chart of accounts

jitin_chawla
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

Check this then :

select SUM(t2.Debit)-SUM(t2.credit), t0.CurrTotal,

t0.acctname, t0.acctcode, t2.FCCurrency

from OACT t0

inner join JDT1 t2 on t0.acctcode = t2.shortname and t2.Account = t2.shortname

where t0.ActType = 'I'

and t2.RefDate >='[%1]' and t2.RefDate<='[%2]'

group by t0.acctname, t0.acctcode,t2.FCCurrency, t0.CurrTotal

Kind Regards,

Jitin

SAP Business One Forum Team

former_member184718
Active Contributor
0 Kudos

Hi,

Try this:

select t1.AcctCode 'Sales Account Code', t1.AcctName 'Sales Account Name', t1.CurrTotal 'Balance' from OACT t1

inner join JDT1 t2 on t1.AcctCode = t2.Account

where t1.ActType ='I'

and t2.RefDate >= '[%1]' and t2.RefDate <= '[%2]'

group by t1.AcctCode, t1.AcctName, t1.CurrTotal

Thanks.

Hari