Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Nominal codes/Ledger A...
Microsoft Dynamics AX (Archived)

Nominal codes/Ledger Account numbers against vendor invoice transactions SQL

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi all,

I'm really stuck with this. I only have a rudimentary understanding of accounting procedure but my company are really breathing down my neck for me to come up with this report. I need the total payment to a given supplier along with the posting type and account name.

I'm looking at the VENDTRANS table and I need the total spend for a supplier over a given time period. I think that's as simple as the below query, which ties with with the results I get when running the supplier invoice transactions report in the system.

SELECT ACCOUNTNUM, CAST(TRANSDATE as date), VOUCHER, AMOUNTMST
FROM [FGAX62LIVE].[dbo].[VENDTRANS]
where ACCOUNTNUM = 'accno'
and TRANSDATE between '2018-01-01' and '2018-03-31'

The problem I have is that I cannot seem to tie our nominal codes against this. I can find the numbers in the MAINACCOUNT table as MAINACCOUNTID and as part of the LEDGERACCOUNT field in GENERALJOURNALENTRY table. I can't seem to link these properly to the VENDTRANS table though. 

If anyone could shed some light on this it would be much appreciated!

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Nominal codes/Ledger Account numbers against vendor invoice transactions SQL

    Thank you for your responses, they are much appreciated.

    Syed Fasih Ur Rehman, I found that I could link the VENDTRANS(vt) table to the LEDGERJOURNALTRANS(jt) on vt.RECID=jt.VENDTRANSID but I was concerned that the data in jt might be volatile. 

    André Arnaud de Calavon, while there are invoices posted from POs that are also payments based on journals that do not have corresponding POs. Trying to capture everything doesn't seem easy. I can look at the two General Journal tables linked as below and that gives me the code I'm looking for under the LEDGERACCOUNT field but I don't know how to link that back to the VENDTRANS table as I need the report I'm writing to drill down from high level spend per account type/procurement code all the way to the detail of individual lines.

    select * from [dbo].[GENERALJOURNALENTRY] as je
    left outer join [dbo].[GENERALJOURNALACCOUNTENTRY] as ae
    on je.RECID = ae.GENERALJOURNALENTRY

     

     

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    RE: Nominal codes/Ledger Account numbers against vendor invoice transactions SQL

    Hi Styler,

    Can you tell how you do the invoice registrations for your vendors? Is it always based on journals or do you posting the invoices from the PO directly? If you are using the PO directly, can you tell if you did set the transfer rule settings to transfer in detail or summarized?

    FYI. I would not suggest to use the LedgerJournalTrans table as these might be deleted after a while. You mentioned the GeneralJournalEntry and GeneralJournalAccountEntry yourself which are better. What exact main account are you looking for? The vendor account, expense/inventory or sales tax?

  • Suggested answer
    Syed Fasih Profile Picture
    Syed Fasih 2,442 on at
    RE: Nominal codes/Ledger Account numbers against vendor invoice transactions SQL

    The nominal account codes are not part of VendTrans Table as those are covered in LedgerJourTrans. You can find out the ledger account number by 2 ways:

    1. Go to Accounts payable posting profile and see which ledger account is specified there. It will be the account in which all transactions will flow to.

    2. You can find out the voucher number from the VendTrans table and then use the same voucher number in LedgerJournalTrans to find the main account in which the posting was made.

    Please mark the answer as verified if this resolves your question.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans