Sales Order Query

[Former Member]

Hi need a query that has:

Sales Order #, Customer Name, Customer PO, Sales Order Date, INVOICE DATE, Qty Ordered, Qty Shipped, Item Code

I can not seem to get ACTUAL invoice date to work properly:

[Former Member]

Hi,

You may try this first:

SELECT T0.[DocNum] 'SO #', T0.[CardName] 'Customer Name', T0.NumAtCard 'Customer PO', T0.[DocDate] 'SO Date', T3.[DocDate] 'Invoice Date', T1.[Quantity] 'QTY Ordered', (T1.Quantity - T1.Openqty) 'QTY Shipped', T1.[ItemCode]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

left join INV1 T2 on T2.[BaseEntry] = t1.Docentry and T2.[BaseLine]  =  T1.[LineNum] and T2.[BaseType] = '17'

LEFT JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry]

WHERE T0.[DocDate] between [%0] and [%1]

However, if you have delivery in the process, this query will not return the required dates.

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

Please try this query:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[DocDate] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join INV1 T2 on T2.[BaseEntry] = t1.Docentry and

T2.[BaseLine]  =  T1.[LineNum]  INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1] and T2.[BaseType] = '17'

Thanks.

[Former Member]

Hi Daniel,

Try this..

SELECT distinct T0.[DocDueDate], T0.[CardName],T0.[DocNum], T0.[CardName], T1.[Dscription], T1.[Quantity], T7.docnum 'Invoice #',T9.docnum 'Delivery#', T0.DocNum 'SO#' FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode LEFT JOIN OTER T5 ON T4.Territory = T5.territryID LEFT join dln1 T8 on T8.baseentry = t0.docentry and t1.linenum = T8.baseline LEFT join odln T9 on T9.docentry = T8.docentry LEFT JOIN INV1 T6 ON T6.BaseEntry=T9.DocEntry LEFT JOIN OINV T7 ON T7.DocEntry=T6.DocEntry

Regards,

SP Samy

[Former Member]

Hi!

It is "pulling" incorrect matching invoice # and I need invoice Date?