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) / Open Customer Credit Memo
Microsoft Dynamics AX (Archived)

Open Customer Credit Memo

(0) ShareShare
ReportReport
Posted on by AdnDalhi 336

Hello guys,

I have a quick a quick technico - functional question,

1. From where i can see the Open Customer Credit Memo in AX 2012, and how functionally it's work (What is a customer credit Note ? When we can create a Credit Note? , From where i can see the Open Credit Memo in AX?, and what is the difference between Credit memo  and credit note? they are the same ?)

2. I would like to create a SQL query to pull all Open Customer Credit Memo, ( i would like to know the appropriate tables to use )

Thanks in advance ;)

*This post is locked for comments

I have the same question (0)
  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Any advices please guys, i will gratefull, thanks :)

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,119 Super User 2025 Season 2 on at

    Hi Adax,

    1) A credit memo and credit note is the same. Acutally it is a negative invoice. You can use all kind of invoice forms or reports to also get information about credit notes.

    2) You can query on the CustTrans and/or CustTransOpen tables.

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi André,

    Thanks for your answer,

    1) If i well understand it's just a negatif Total Invoice amount, is there any specific form in AX to check it for Customer Open Invoices / or Free Text invoices Forms ?

    2) What about the CustInvoiceJour table also ? and which field exactly i need to check, to verify if it's Negatif or not (AmountMST on CustTransOpen) ?

    Thanks

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Adax,

    A couple of days ago you raised a similar question on how to filter open customer invoices.

    community.dynamics.com/.../348368

    You should be able to apply and extend your solution to customer credit notes simply by filtering for negative invoices.

    Best regards,

    Ludwig

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi Ludwig,

    Thanks a lot for your answer,

    Yes, it was my previous ticket, and i found the solution :)

    so, i should just filter on Negative Invoice invoice ?

    CUSTINVOICEJOUR.INVOICEAMOUNTMST  AS [Invoice amount] and check only negatives ?

  • Verified answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Adax,

    Right. A simple selection of the negative amounts should give you the credit notes.

    Best regards,

    Ludwig

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,119 Super User 2025 Season 2 on at

    Hi Adax,

    You can use the CustInvoieJour table, but it has no status if it is still an open invoice or not. For that, you need to link to the CustTrans and/or CustTransOpen.

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi André,

    Yes for sure , i make a join between them, CustranOpen ( to retrieve only Open ones) , CustTrans and CustranInvoiceJour,

    My question is just which field of InvoiceAmoutn( AmountMST) to check,  now i'm checking the one in CustInvoiceJour.InvoiceAmount

  • AdnDalhi Profile Picture
    AdnDalhi 336 on at

    Hi André,

    Yes for sure , i add a join between them, CustranOpen ( to retrieve only Open ones) , CustTrans and CustInvoiceJour,

    My question is just which field of i need to check, now i'm checking the " CustInvoiceJour.InvoiceAmount"

    And here is my Query. a WHEN CASE Statement on invoiceAmount to pull only negatives.

    SELECT

    CUSTTRANS.CREATEDDATETIME AS [Created date time],

    CUSTTRANS.INVOICE AS [Invoice Id],

    CUSTTRANSOPEN.ACCOUNTNUM AS [Customer account],

    CUSTINVOICEJOUR.INVOICEACCOUNT AS [Invoice account],

    CUSTINVOICEJOUR.CUSTGROUP AS [Customer group],

    CUSTINVOICEJOUR.INVENTLOCATIONID AS [Warehouse],

    LOGISTICSPOSTALADDRESS.ADDRESS AS [Invoice address],

    LOGISTICSPOSTALADDRESS.STREET AS [Street],

    LOGISTICSPOSTALADDRESS.ZIPCODE AS [Zipcode],

    LOGISTICSPOSTALADDRESS.CITY AS [City],

    LOGISTICSPOSTALADDRESS.COUNTY AS [County],

    LOGISTICSPOSTALADDRESS.COUNTRYREGIONID AS [Country region Id],

    (CASE WHEN CUSTINVOICEJOUR.INVOICEAMOUNTMST < 0 THEN CUSTINVOICEJOUR.INVOICEAMOUNTMST END) AS [Invoice amount],

    CUSTTRANS.SETTLEAMOUNTMST AS [Payment amount],

    CUSTINVOICEJOUR.INVOICEAMOUNTMST - CUSTTRANS.SETTLEAMOUNTMST AS [Balance],

    CUSTTRANSOPEN.TRANSDATE AS [Transaction date],

    CUSTTRANSOPEN.DUEDATE AS [Due date],

    CUSTTRANS.CURRENCYCODE AS [Currency code],

    CUSTTRANSOPEN.DATAAREAID AS [Data area Id]

    FROM CUSTTRANSOPEN , CUSTTRANS, CUSTINVOICEJOUR, LOGISTICSPOSTALADDRESS

    WHERE CUSTTRANSOPEN.ACCOUNTNUM = CUSTTRANS.ACCOUNTNUM

    AND CUSTTRANSOPEN.REFRECID = CUSTTRANS.RECID

    AND CUSTTRANSOPEN.DATAAREAID = '303'

    AND CUSTINVOICEJOUR.INVOICEACCOUNT = CUSTTRANSOPEN.ACCOUNTNUM

    AND CUSTINVOICEJOUR.INVOICEID = CUSTTRANS.INVOICE

    AND CUSTINVOICEJOUR.INVOICEACCOUNT = CUSTTRANS.ACCOUNTNUM

    AND CUSTINVOICEJOUR.INVOICEDATE = CUSTTRANS.TRANSDATE

    AND CUSTINVOICEJOUR.LEDGERVOUCHER = CUSTTRANS.VOUCHER

    AND CUSTINVOICEJOUR.INVOICEPOSTALADDRESS = LOGISTICSPOSTALADDRESS.RECID

    ORDER BY CUSTTRANS.CREATEDDATETIME

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,119 Super User 2025 Season 2 on at

    Hi Adax,

    There are multiple amount fields which you can use, but CustInvoiceJour.InvoiceAmount is an excellent choice.

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