Query to select Invoiced SO's partial and Credit notes


I need to create a query which will fetch all the Invoiced Sales orders. It should also fetch partially invoiced SO’s and the SO’s which has credit notes against it.
How can get this query.

Initially i had written the following:

While select CustInvoicejour where

CustInvoiceJour.SalesStatus = Sales

while select custinvoicetrans

where custinvoice trans.rel1 = custinvoicejour.rel1

&& custinvoice trans.rel2 = custinvoicejour.rel2



My understanding is that when an SO is invoiced it will create record in CustInvoiceJour and Trans.
Is this correct? or do i need to follow something else?

Don’t forget that you may have a single CustInvoiceJour for several sales orders, if you use summary updates.

I believe that the CustInvoiceSalesLink table is the best data source for you query.

This table does not contain all the fields that i need for my method. all the fields are available in CustinvoiceJour and CustinvoiceTrans. will i need to Join CustInvoiceSalesLineLink and custInvoiceJour & custinvoiceTrans?

I would really apprecite a pseudo query with links?

thanks in Advance.

Of course you can join tables. I don’t know which fields you need, but your original requirement was to “fetch all the Invoiced Sales orders”, not invoices. Sales orders are saved in SalesTable and SalesLine tables; CustInvoiceJour and CustInvoiceTrans are invoices. These are two different things.