Opening Balance, Credit and Debit in Trial Balance using SQL

(1) Share

Report

Posted on by SP-22072258-0

Hi,

I'm recreating the trial balance that appears in AX 2012 in sql server. Rigth now, I have the closing balance but I want to get the opening balance, debit and credit. Is there a way that I can get those values? This is the query I have:

SELECT m.[PARTITION], m.MAINACCOUNTID, SUM(a.ACCOUNTINGCURRENCYAMOUNT) AS ACCOUNTINGCURRENCYAMOUNT
  FROM GENERALJOURNALACCOUNTENTRY a
  JOIN GENERALJOURNALENTRY e  ON e.[PARTITION] = a.[PARTITION] AND e.RECID = a.GENERALJOURNALENTRY
  JOIN MAINACCOUNT m          ON m.[PARTITION] = a.[PARTITION] AND m.RECID = a.MAINACCOUNT
  JOIN FISCALCALENDARPERIOD p ON p.[PARTITION] = e.[PARTITION] AND p.RECID = e.FISCALCALENDARPERIOD
  JOIN FISCALCALENDARYEAR FCY ON FCY.RECID = p.FISCALCALENDARYEAR
  WHERE p.[TYPE] = 1  -- 0=Opening,1=Operating,2=Closing
   AND m.MAINACCOUNTID = '111406'
   AND FCY.NAME = '2024'
  GROUP BY m.[PARTITION], m.MAINACCOUNTID
  ORDER BY m.[PARTITION], m.MAINACCOUNTID

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

Raj Borad 1,578 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

You can get the Debit and Credit with the use of IsCredit Field of GeneralJournalAccountEntry Table in your query.

Thank you,

Raj D Borad

Was this reply helpful? Yes No
SP-22072258-0 84 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Raj,

That worked fine, thank you!

Was this reply helpful? Yes No
Raj Borad 1,578 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Can you please mark my reply as verified Answer.

For opening balance, you can get the closing balance of the previous calendar year.

Ex.: here you need to get the sum(AccountCurrency) for the year '2023'.

Was this reply helpful? Yes No