316
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
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
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
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))
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?
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (