Skip to Content
0
Former Member
Apr 22, 2009 at 08:39 PM

Back Order Report/Query

878 Views

Hi Experts,

I have a client who do not want the Open Sales Orders that is past it's due date to show up on the Sales Back Order report.

I run into some difficulties with the querie:

When I use the Open Quantity and Deloivered Quantity fields in the query it will also bring back the Open SO with no Deliveries linked.

 SELECT T1.[DocNum], T1.[CardCode], T1.[CardName], T1.[DocDate], T1.[DocDueDate], T0.[ItemCode], T0.[Dscription] FROM [dbo].[RDR1]  T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[LineStatus] = 'O' AND T0.[Quantity] <> '0' 

When I use the RDR1 and DLN1 base entry link, it will also show the lines that are already delivered in full.

 SELECT T1.[DocNum], T1.[CardCode], T1.[CardName], T1.[DocDate], T1.[DocDueDate], T0.[ItemCode] FROM RDR1 T0  INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN DLN1 T2 ON T2.BaseEntry = T0.DocEntry and T2.BaseLine = T0.Visorder 

How do I get a query that displays only the Sales Orders that have Backorders, without also showing the Sales Orders that have nothing delivered but is past due date?

Your help will be greatly appreciated.

Thanks,

Marli