[SOLVED] How can I do query which will select GL/Accounts with their amount, Turnover and Operating Costs? SAP B1

How can I do query which will select GL/Accounts with their amount, Turnover and Operating Costs? SAP B1

How can I do query which will select GL/Accounts with their amount of Turnover and Operating Costs in Chart of Accounts?

For example

SELECT GL/Account,Amount FROM OACT WHERE their_type='Turnover'

SELECT GL/Account,Amount FROM OACT WHERE their_type='Operating Costs'

I tried to search in OACT table of SAP B1 but there is no column which stores Turnover, Operating Costs or Assets,....

How can I do the above query without set condition of one by one account?

for example

SELECT GL/Account,Amount FROM OACT WHERE AcctCode='0001' or AcctCode='0002' or AcctCode='0003' or AcctCode='0004'

Please anyone can help me

Solution

You will have to join the OACT table with JDT1 that has the amounts.

I am not sure of the Rwandan standard for chart of accounts but you can have something like:

SELECT T0.AcctCode, T0.AcctName, T0.FatherNum, SUM(T1.Debit - T1.Credit) * -1 AS 'Amount'
FROM OACT T0
INNER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
WHERE T0.AcctCode LIKE '41%' AND T0.Postable = 'Y'
AND (T1.RefDate BETWEEN {?StartDate} AND {?EndDate})

GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum

Above is assuming that all accounts starting with 41 refer to turnover. Also not sure whether all posting you done are done on one level, e.g. 4 or on multiple levels.