Finance | Project Operations, Human Resources, ...

Connect VendInvoiceJour to GeneralJournalAccountEntry

(3) Share

Report

Posted on by KL-25041631-0

I am having a hard time understanding how to build out a GL Report that would retrieve all the data from generaljournalaccountentry. This is my current query but I want to join vendinvoicejour and vendinvoicetrans to get the vendor side of it. But there is not a direct connection between the tables.

SELECT DISTINCT G.SUBLEDGERVOUCHER, GA.GENERALJOURNALENTRY,GA.LEDGERACCOUNT, GA.TRANSACTIONCURRENCYAMOUNT, GA.ISCREDIT, G.ACCOUNTINGDATE,
MA.MAINACCOUNTID,
CASE
WHEN GA.ISCREDIT = 1 THEN GA.TRANSACTIONCURRENCYAMOUNT
ELSE 0.00
END AS CREDITAMOUNT,

CASE
WHEN GA.ISCREDIT = 0 THEN GA.TRANSACTIONCURRENCYAMOUNT
ELSE 0.00
END AS DEBITAMOUNT
FROM GENERALJOURNALACCOUNTENTRY GA
JOIN GENERALJOURNALENTRY G ON G.RECID = GA.GENERALJOURNALENTRY
JOIN MAINACCOUNT MA ON MA.MAINACCOUNTID = SUBSTRING(GA.LEDGERACCOUNT,0,CHARINDEX('-',GA.LEDGERACCOUNT,0))

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Sohaib Cheema 47,259 User Group Leader on at

Like (0)

Report

Connect VendInvoiceJour to GeneralJournalAccountEntry

Hi @KL-25041631-0

All the 6 columns you want, for that do not need to go GL tables, to get on report. Here is the SQL query that I have quickly created for you and that shows how you can get the 6 columns that you want.

Note: I have used where clause during sample testing, you can modify it as per need and further improve it for performance or other reasons.

Select

VendInvoiceJour.PurchId as [PO Number]
,VendInvoiceJour.LedgerVoucher as [Voucher]
,VendInvoiceJour.InvoiceDate as [Invoice Date]
,VendInvoiceTrans.ItemId
,VendTable.AccountNum as [Vendor Account]
,DirPartyTable.Name as [Vendor Name]

from VendInvoiceTrans

join VendInvoiceJour
on VendInvoiceJour.PurchId = VendInvoiceTrans.PurchId
AND VendInvoiceJour.InvoiceId = VendInvoiceTrans.InvoiceId
AND VendInvoiceJour.InvoiceDate = VendInvoiceTrans.InvoiceDate
AND VendInvoiceJour.numberSequenceGroup = VendInvoiceTrans.numberSequenceGroup
AND VendInvoiceJour.InternalInvoiceId = VendInvoiceTrans.InternalInvoiceId
AND VendInvoiceJour.DataAreaId = VendInvoiceTrans.DataAreaId

join VendTable
on VendTable.AccountNum = VendInvoiceJour.OrderAccount
AND VendTable.DataAreaId = VendInvoiceJour.DataAreaId

Join DirPartyTable
on VendTable.Party = DirPartyTable.RecId

Where VendInvoiceJour.PurchId = '000005' and VendInvoiceJour.DataAreaId = 'USMF'

Apart from that if you need to join the invoice with the GL tables, that question has been answered many time. You can see that on a old thread and on many blogposts. Link to old thread

Was this reply helpful? Yes No
KL-25041631-0 8 on at

Like (0)

Report

Connect VendInvoiceJour to GeneralJournalAccountEntry

@Sohaib Cheema the columns that I would essentially need would be the PO#, Voucher Number, Accounting Date, Product Number/Item Number, Vendor Account Number, and Vendor Name.

Was this reply helpful? Yes No
Sohaib Cheema 47,259 User Group Leader on at

Like (0)

Report

Connect VendInvoiceJour to GeneralJournalAccountEntry

Hi @KL-25041631-0

You need to go from Vendor invoice >> SubLedger >> Journal

If you could show us the layout/columns of your report, we can try to help

1 people found this reply helpful.

Was this reply helpful? Yes No
Martin Dráb 235,609 Most Valuable Professional on at

Like (0)

Report

Connect VendInvoiceJour to GeneralJournalAccountEntry

Moved from Integration, Dataverse, and general topics forum to Finance | Project Operations, Human Resources, AX, GP, SL forum, because the table and fiels names match tbhose existing in F&O.

Also, check the previous discussion in Relationship between GeneralJournalAccountEntry and LedgerJournalTrans.

Was this reply helpful? Yes No