web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to get list of inv...
Microsoft Dynamics AX (Archived)

How to get list of invoices settled by a specific payment Journal- cross company settlements

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I have been asked to create a sql report from our AX 2012 database, to show invoices settled by payment journals in LedgerJournalTable/LedgerJournalTrans. There is a field on LedgerJournalTrans called MarkedInvoice, but if there is more than one invoice matched to the payment voucher, it just shows as "*"- I need to get the list of invoice numbers instead of the *

I managed to join to CustTrans table (LedgerJournalTrans.Voucher = CustTrans.Voucher), which works when the settlement and the matched invoices are all in the same company, but we have a lot of cross-company settlements so for those the voucher number from LedgerJournalTrans does not exist in CustTrans. I know that for cross-company settlements AX creates a new voucher somewhere, but I am not sure how to find that new voucher number, what tables to look in etc. Can anyone point me in the right direction please?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi SophieDurrant,

    LedgerJournalTrans creates CustTrans fro payment and then you settle it with CustTrans for Invoice. Those 2 custTrans are connected by custSettlement table.

    You can find invoice transaction settled using next x++ query that you can easily transform into T-SQL query:

    CustTrans custTransOffset;
CustTrans custTrans;
CustSettlement custSettlement;

while select custTransOffset
   where custTransOffset.Invoice
       join custSettlement
           where custSettlement.OffsetRecid == custTransOffset.RecId
               exists join custTrans
                   where custTrans.RecId        == custSettlement.TransRecId
                      && custTrans.DataAreaId == custSettlement.TransCompany
                      && custTrans.AccountNum == custSettlement.AccountNum
                      && custTrans.Voucher    == _ledgerJournalTrans.Voucher
                      && custTrans.TransDate  == _ledgerJournalTrans.TransDate
{
}

    This query is not 100% correct from AX point of view because does not have crosscompany to handle crosscompany settlement, but it should not be an issue for you in SQL. 

  • Community Member Profile Picture
    Community Member on at

    Thanks for your reply but, unfortunately it does not work!

    The problem is for cross-company settlements, as far as I can tell, the voucher from LedgerJournalTrans does not exist in CustTrans or in CustSettlement. I think I need to find the new voucher number that AX creates to match against the invoice in the other company but I don't know where to look for it.

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    As I said you should have 2 custtrans: one in the journal company and voucher would be the same, another in different company and you should be able to reach it using custsettlement because it uses recid and offset company I'd.

    Please show us your SQL query

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    I think I have managed to crack it!

    LedgerJournalTrans has Company (where the invoice is) and OffsetCompany (where the payment has been entered). If Company and OffsetCompany are not the same then that makes it a cross-company payment- the voucher from the OffsetCompany does not exist in CustTrans in either of the companies.

    I went to GeneralJournalEntry (LJT.Voucher = GJE.SubLedgerVoucher), and there I saw that there were additional transactions for the cross-company payments but they all had the same CreatedTransactionId on GJE. So I selected JournalNum from LJT where OffsetCompany <> Company, joined to GJE to get the CreatedTransactionId and put that in a subquery (which I named CC).

    From the CC subquery I joined to GJE (CC.CreatedTransactionId = GJE.CreatedTransactionId) and then to LedgerEntryJournal (GJE.LedgerEntryJournal = LEJ.RecId and LEJ.JournalNumber <> CC.JournalNum) to get the journal number(s) from the other company and put that in another subquery (which I named CCJ).

    Finally from that CCJ subquery, I could join back to LedgerJournalTrans to get all of the voucher numbers associated with that journal and then was able to join to CustTrans / CustSettlement to get the settled invoices in the way you had suggested. I have checked quite a few payment transactions and it looks like it works, so will mark this as the answer in case others have the same problem.

    Thanks for your input :)

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans