Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 Release Wave 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 1 virtual launch event
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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 ;)
Any advices please guys, i will gratefull, thanks :)
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.
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) ?
A couple of days ago you raised a similar question on how to filter open customer invoices.
You should be able to apply and extend your solution to customer credit notes simply by filtering for negative invoices.
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 ?
Right. A simple selection of the negative amounts should give you the credit notes.
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.
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
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.
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
There are multiple amount fields which you can use, but CustInvoiceJour.InvoiceAmount is an excellent choice.
Thanks, ok, i will check the "AmountMST" on CustTranOpen table i think it's the corect one.
Because here i need it for Open ones, and i'm agree with you that CustTransOpen Table is the most correct :)
I marked your answer as verified, thanks again for this exchange André :)
The CustInvoiceJour.InvoiceAmount is the total invoice amount. The CustTransOpen table only has the amounts that has not been settled. In case of a partially paid invoice, the CustTransOpen does not provide the invoice total.
Business Applications communities