SQL - Balancing GL and AP Transactions

(0) Share

Report

Posted on by SU-22040800-0

2,459

Does anyone have a good SQL Statement to run (in AX2012 DB) to balance GL transactions, and Vendor Transactions?

I can get the GL transactions total using this:

SELECT GJE.PARTITION[PARTITION],SUBLEDGERVOUCHERDATAAREAID[CPNY],GJE.ACCOUNTINGDATE[DATE],GJE.SUBLEDGERVOUCHER[VOUCHER],SUM(GJAE.ACCOUNTINGCURRENCYAMOUNT)[GL AMOUNT]
                     FROM GENERALJOURNALACCOUNTENTRY GJAE JOIN GENERALJOURNALENTRY GJE ON (GJAE.GENERALJOURNALENTRY=GJE.RECID)
                     WHERE LEFT(GJAE.LEDGERACCOUNT,X) IN  ('MY AP ACCOUNTS')
                     GROUP BY GJE.PARTITION,GJE.SUBLEDGERVOUCHERDATAAREAID,GJE.ACCOUNTINGDATE,GJE.SUBLEDGERVOUCHER

This then balances (by day), to the Trial Balance in AX.

I am now looking to get a balance from e.g. VendTrans (by Day, by Voucher) with the GL entries.

Anyone had any experience with this?

I have the same question (0)