Dynamics Community Forum Thread Details

Hello everyone,

I need some help with a query I’m working on.

I’m trying to build an accounting report that mirrors the exact view shown in D365 under General ledger > Inquiries and reports > Voucher transactions. I was able to successfully recreate that view in SQL using the query below:

[SELECT DISTINCT
G.DATE,
G.LEDGERNAME,
G.MAINACCOUNTID,
G.LEDGERACCOUNT,
G.NAME,
G.JOURNALNUMBER,
G.VOUCHER,
G.DOCUMENTNUMBER,
G.CREDIT,
G.DEBIT,
G.TAMOUNT,
V.INVOICEACCOUNT AS VENDORACCOUNT,
D.NAME AS VENDORNAME,
V.PURCHID AS PONUMBER,
G.TYPE,
G.POSTINGTYPE,
C.INVOICEACCOUNT AS CUSTOMERACCOUNT,
C.INVOICINGNAME AS CUSTOMERNAME
FROM GeneralLedgerTable G
LEFT JOIN VENDINVOICEJOUR V ON V.LEDGERVOUCHER = G.VOUCHER
LEFT JOIN VENDINVOICETRANS T ON T.INVOICEID = V.INVOICEID
LEFT JOIN VENDTABLE VT ON VT.ACCOUNTNUM = V.INVOICEACCOUNT
LEFT JOIN DIRPARTYTABLE D ON D.RECID = VT.PARTY
LEFT JOIN CUSTINVOICEJOUR C ON C.LEDGERVOUCHER = G.VOUCHER]

The complexity comes in with Posting Types 82 and 236. For these posting types, I want to display the related PO lines, including the item ID, item name, quantity, and line amount. Although I can join the PO line tables to my current query, this causes duplication because the join occurs at the MainAccount level. For example, voucher APO00900 has two voucher transaction lines, both with Posting Type 236. One line is posted to MainAccount 12350, and the other is posted to MainAccount 67890. Both lines reference the same PurchId (PO-09090).When reviewing the purchase order, it contains three lines. Two of those PO lines roll up to the total posted to MainAccount 12350, while the remaining PO line matches the transaction amount posted to MainAccount 67890.If I add the PO lines directly to my current query, each PO line ends up matching both main accounts. This results in six records being returned instead of the expected three. I have been trying to determine whether there is another entity or relationship that can be used to correctly associate the voucher transaction, the main account, and the corresponding PO line(s) but found none.

Please let me know if I can provide any additional information. I would really appreciate any guidance!

[I created a view to make things cleaner for the GL Data:

SELECT GJ.LEDGERACCOUNT, SUBSTRING(GJ.LEDGERACCOUNT,0,CHARINDEX('-',GJ.LEDGERACCOUNT,0)) AS MAINACCOUNTID,GJ.TEXT AS DESCRIPTION, GJ.TRANSACTIONCURRENCYAMOUNT AS TAMOUNT,
CASE WHEN GJ.ISCREDIT = 1 THEN GJ.TRANSACTIONCURRENCYAMOUNT END AS CREDIT,
CASE WHEN GJ.ISCREDIT = 0 THEN GJ.TRANSACTIONCURRENCYAMOUNT END AS DEBIT,
GJ.POSTINGTYPE AS TYPE, GJ.POSTINGTYPE_$LABEL AS POSTINGTYPE,
G.ACCOUNTINGDATE AS DATE, G.JOURNALCATEGORY, G.DOCUMENTNUMBER, G.JOURNALNUMBER, G.SUBLEDGERVOUCHER AS VOUCHER, UPPER(G.SUBLEDGERVOUCHERDATAAREAID) AS LEDGERNAME, G.JOURNALCATEGORY_$LABEL, G.RECID,
MA.NAME
FROM GENERALJOURNALACCOUNTENTRY GJ
JOIN GENERALJOURNALENTRY G ON G.RECID = GJ.GENERALJOURNALENTRY
JOIN MAINACCOUNT MA ON MA.MAINACCOUNTID = SUBSTRING(GJ.LEDGERACCOUNT,0,CHARINDEX('-',GJ.LEDGERACCOUNT,0))
WHERE SUBSTRING(GJ.LEDGERACCOUNT,0,CHARINDEX('-',GJ.LEDGERACCOUNT,0)) > 'x9999' ]

Categories:

Dynamics 365 Finance

Community site session details

PO Lines to Voucher

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates