Skip to Content
0

How to show quote number on Sales Order type report

Dec 08, 2017 at 03:38 PM

33

avatar image

I have a report that I run each month:

SELECT T3.[DocNum] AS 'Order #' , T3.[DocStatus] AS 'Document Status' , T3.[CardCode] AS 'Customer Code' , T3.[CardName] AS 'Customer Name' , T3.[DocDate] AS 'Posting Date' , T3.[TaxDate] AS 'Document Date' , T3.[DocDueDate] AS 'Due Date' , T2.[ItemCode] AS 'Item No.' , T1.[ItemName] AS 'Item Description' , T1.[ItmsGrpCod] AS 'Item Group' , T0.[ItmsGrpNam] AS 'Group Name' , T2.[Quantity] AS 'QTY' , T4.[Price] AS 'Cost in PriceList' , T2.[GrossBuyPr] AS 'Cost from Order' FROM [dbo].[OITB] T0 INNER JOIN [dbo].[OITM] T1 ON T1.[ItmsGrpCod] = T0.[ItmsGrpCod] INNER JOIN [dbo].[RDR1] T2 ON T2.[ItemCode] = T1.[ItemCode] AND T2.[ItemCode] = T1.[ItemCode] INNER JOIN [dbo].[ORDR] T3 ON T3.[DocEntry] = T2.[DocEntry] Left Outer Join ITM1 T4 on T4.PriceList = '23' and T1.ItemCode = T4.ItemCode WHERE T0.[ItmsGrpCod] Like 105 OR T0.[ItmsGrpCod] Like 210 and T3.[DocDate] >=CONVERT(DATE,SUBSTRING(CONVERT(VARCHAR, DATEADD(m, -1,GETDATE()),112),1,6)+'01' ) and T3.[DocDate] <=DATEADD(d,-1,SUBSTRING(CONVERT(VARCHAR, GETDATE(),112),1,6)+'01') ORDER BY T3.[DocNum]

This report works flawlessly - thank you to anyone who helped me. I have just recently been asked to show the origin quote number. I have found this which does show Sales Order to Sales Quote matching.

SELECT T0.[DocNum] as SalesQuatation,T3.[DocNum] as SalesOrder FROM OQUT T0 INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN RDR1 T2 ON T0.DocNum=T2.BaseRef LEFT OUTER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry

How do I merge these 2 into 1 pretty report?

Thank you,

Keith

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Inga Babco Dec 13, 2017 at 01:37 PM
0

SELECT T3.[DocNum] AS 'Order #' , T3.[DocStatus] AS 'Document Status' , T3.[CardCode] AS 'Customer Code' , T3.[CardName] AS 'Customer Name' , T3.[DocDate] AS 'Posting Date' , T3.[TaxDate] AS 'Document Date' , T3.[DocDueDate] AS 'Due Date' , T2.[ItemCode] AS 'Item No.' , T1.[ItemName] AS 'Item Description' , T1.[ItmsGrpCod] AS 'Item Group' , T0.[ItmsGrpNam] AS 'Group Name' , T2.[Quantity] AS 'QTY' , T4.[Price] AS 'Cost in PriceList' , T2.[GrossBuyPr] AS 'Cost from Order' ,

T2.BaseRef as 'Quote #'

FROM [dbo].[OITB] T0 INNER JOIN [dbo].[OITM] T1 ON T1.[ItmsGrpCod] = T0.[ItmsGrpCod]

INNER JOIN [dbo].[RDR1] T2 ON T2.[ItemCode] = T1.[ItemCode]

INNER JOIN [dbo].[ORDR] T3 ON T3.[DocEntry] = T2.[DocEntry]

Left Outer Join ITM1 T4 on T4.PriceList = '23' and T1.ItemCode = T4.ItemCode

WHERE (T0.[ItmsGrpCod] Like 105 OR T0.[ItmsGrpCod] Like 210)

and T3.[DocDate] >=CONVERT(DATE,SUBSTRING(CONVERT(VARCHAR, DATEADD(m, -1,GETDATE()),112),1,6)+'01' ) and T3.[DocDate] <=DATEADD(d,-1,SUBSTRING(CONVERT(VARCHAR, GETDATE(),112),1,6)+'01')

ORDER BY T3.[DocNum]

Share
10 |10000 characters needed characters left characters exceeded