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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SSRS: How Can I Get Th...
Microsoft Dynamics AX (Archived)

SSRS: How Can I Get The Original Invoice ID From a Credit Invoice?

(0) ShareShare
ReportReport
Posted on by Momochi 1,036

Hello awesome community! :)

There is a sales order with 2 invoices. the 2nd invoice is a return of the 1st invoice. they have the same amount but the returned one is in negative, but they have different invoice IDs.

My issue is, there is a custom report which runs on the invoice level, that uses CustInvoiceJour and CustInvoiceTrans tables. They asked me to show in the report the original invoice ID if the report did run on the returned invoice (Credit Note). I couldn't find a relation between the the original invoice id and the credit invoice ID. I tried checking the CustVendInvoicingJourHeader Table but it's empty.

The only way I found was to check the SalesLine.InventTransIdReturn (Returned Lot ID Field) if there is a returned item, then I can get the original SalesLine.InventTransID and then I can get the original InvoiceID from there. But maybe it will be a problem if the sales order number of the credit invoice was different....

Is there a more simple or more direct way to get the original Invoice ID from a credit invoice ID?

Any advice would be appreciated!

Thanks in advance!

*This post is locked for comments

I have the same question (0)
  • Momochi Profile Picture
    Momochi 1,036 on at
    RE: SSRS: How Can I Get The Original Invoice ID From a Credit Invoice?

    Actually using SalesLine.ReturnInventTransID is not correct since this table only has SalesID number and it can't lead me to the correct invoice id if the sales order has multiple invoices....

    What should I do?

  • Guy Terry Profile Picture
    Guy Terry 28,924 Moderator on at
    RE: SSRS: How Can I Get The Original Invoice ID From a Credit Invoice?

    Hi Momochi,

    If they are manually creating a negative line on the Sales order, AX does not know they are crediting the first invoice on that Sales order. There is no way to mark that this negative line is for the invoice on that same sales order..... except that they are on the same sales order. So, if that is the normal process, you could just look for other Invoices with the same SalesId.

    Alternatively, there are other ways to create the credit. If the negative line is created using the Create -> Credit note function, or if a Return order is used, and if the 'Find a sales order' function is used, then user will select a previous invoice which they wish to raise a credit for. In this case, if you examine the lines of the new sales line or Return order, you might find that:

    • SalesLine.InventTransIdReturn contains the LotId of the original Sales line.

    • SalesLine.RefReturnInvoiceTrans_W contains the RecId of the original Customer Invoice line.

  • Suggested answer
    Momochi Profile Picture
    Momochi 1,036 on at
    RE: SSRS: How Can I Get The Original Invoice ID From a Credit Invoice?

    I found a way to get it.

    What I did is, since I have the Invoice ID of the credit note in "CustInvoiceTrans", I did a select in "InventTrans" Table where Invoice ID = Credit Note Invoice ID, Then, there is a field in "InventTrans" table called "InventTransOrigin" and "ReturnInventTransOrigin".

    The"ReturnInventTransOrigin" contains the "InventTransOrigin" ID of the original Invoice ID line in "InventTrans".

    So I made a 2nd select from "InventTrans" where ReturnInventTransOrigin (of the previous select from the credit note Invoice ID) == InventTransOrigin.

    And now the line we get from this select contains the original Invoice ID.

    In short, we make two select statements from "InventTrans" first using the invoice ID from "CustInvoiceTrans", and the 2nd one using the the ReturnInventTransOrigin id we got from the first select.

    Hope it helps others :)

    And thanks for the help.

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…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Alexey Lekanov Profile Picture

Alexey Lekanov 3

#3
Willem van Duren Profile Picture

Willem van Duren 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans