Create Query to Show Sales Order Number, Delivery and Invoice

[Former Member]

Hi Experts

I would like to create a query based on Sales Invoices which will show me all sales invoices for BPs with a particular property (Properties 9) - I also need it to display the Delivery Order number and Sales Order numbers which the invoice was based on.

I can do the simple bit to show the Invoice Amount, Tax etc, but can't figure out how to write the SQL to join the RDR1, DLN1 and to the OINV.

Thanks

Jon

[Former Member · Accepted solution]

Hi Jon,

Try this one:

SELECT Distinct T1.docnum Invoice#, T1.docdate, T1.NumAtCard 'Customer PO#', T1.cardcode 'Customer Code', T2.cardname 'Customer Name', T6.DocNum Delivery#, T5.DocNum 'Sale Order#'

FROM dbo.inv1 T0

INNER JOIN dbo.oinv T1 on T1.docentry = T0.docentry

INNER JOIN dbo.ocrd T2 on T2.CardCode = T1.CardCode

Left JOIN dbo.dln1 T3 on T3.TrgetEntry = T1.DocEntry

Left JOIN dbo.RDR1 T4 on T4.TrgetEntry = T3.DocEntry

Left JOIN dbo.ORDR T5 on T5.DocEntry = T4.DocEntry

Left JOIN dbo.ODLN T6 on T6.DocEntry = T3.DocEntry

WHERE T2.QryGroup9 = 'Y'

Thanks,

Gordon

[Former Member]

Hi...

The link you do it through a field of invoice details. When copies of a document to another, the target document shows in detail a column called BaseRef

SELECT *  FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN ODLN T2 ON T1.BaseRef = T2.DocEntry LEFT JOIN DLN1 T3 ON T2.DocEntry = T3.DocEntry LEFT JOIN ORDR T4 ON T3.BaseRef = T4.DocEntry LEFT JOIN OCRD T5 ON T0.CardCode = T5.CardCode WHERE T5.[QryGroup1] = 'Y'

Modifies the fields * you require and the area T5.[QryGroup1] for the property concerned.

Best regards,

Edited by: Paul Ponce on Oct 29, 2009 5:23 PM