Open Customer Invoices

(0) Share

Report

Posted on by AdnDalhi

336

Hello, AXers :)

i'm asking here, about a quick SQL Query to get all Open Customer Invoices, and which table to use for Open Cust invoices ?

With these fields:

1. CreatedDateTime or the date of creation of the invoice

2.Invoice Id

3. DataAreaId

4. Invoice Date

5.Invoice Account

6.Invoice Address

7.Warehouse.

8.Status

Thanks in advance :)

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hello adax,

You should be able to achieve this in the standard open customer invoices form through personalizations.

Would be great if you could double check and test and let us know the outcome.

Many thanks,

Ludwig

Was this reply helpful? Yes No
André Arnaud de Cal... 305,465 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi Adax,

I don't know the purpose of accessing data directly instead of using inquiries or reports. But if you do insist, you would need to query on CustInvoiceJour/Trans tables and CustTrans/CustTransOpen tables.

Not sure what you mean with the status field. Is it about being an open transaction or not?

Was this reply helpful? Yes No
AdnDalhi 336 on at

Like (0)

Report
Copy link

Link copied!

Hi André,

Thanks for your answer,

I need to retrieve only the Cust Invoice headers, for the status field i believe i can ignore it, because the custTransOpen do not contain this field, and also this table contain only Open Transactions,

The goal, is really just to pull all Cust Open Invoice, with their( CreatedDateTime : Date of creation of this invoice / Cust Account / DataAreaId / Invoice Address / Invoice Amount / Invoice date / Invoice Status (Paid or Open))

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi Adax, I don't know why you need to code something here but probably have a look at the open invoice form that I shared above and how it is generated. It might give you some ideas and insights into what you are looking for. Best regards, Ludwig

Was this reply helpful? Yes No
AdnDalhi 336 on at

Like (0)

Report
Copy link

Link copied!

Hello , @Mohammed El-Khatib

Any help please, i will be grateful.

Thanks

Was this reply helpful? Yes No
Verified answer

AdnDalhi 336 on at

Like (0)

Report
Copy link

Link copied!
My ticket has been resolved, thanks to you all, i've used the following query, and i validated the data extraction,

Thanks to you all, appreciate :)

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], --CUSTINVOICEJOUR.INVOICEAMOUNTMST 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 CUSTINVOICEJOUR.INVOICEACCOUNT = CUSTTRANSOPEN.ACCOUNTNUM AND CUSTINVOICEJOUR.INVOICEID = CUSTTRANS.INVOICE AND CUSTINVOICEJOUR.INVOICEACCOUNT = CUSTTRANS.ACCOUNTNUM AND CUSTINVOICEJOUR.INVOICEDATE = CUSTTRANS.TRANSDATE AND CUSTINVOICEJOUR.LEDGERVOUCHER = CUSTTRANS.VOUCHER ORDER BY CUSTTRANS.CREATEDDATETIME

Was this reply helpful? Yes No