All orders shipped and not invoiced

[Former Member]

Hi all,

I am trying to create a query that shows me all orders that have been shipped and not invoiced. I have tried combining the tables (Sales Order, Delivery, and Invoice) using Left Join but I haven't figured out how to show deliveries that have been delivered but not invoiced.

Best,

Kate

[former_member212181]

Hi Kate,

Please try below query and revert back

Select T3.DocNum[SONo.],T3.NumAtCard[Customer PO No.], T0.DocNum[Del No], T0.DocDate[Del Date]

  ,T1.ItemCode, T1.Quantity, T1.OpenQty, T1.LineStatus[Del Line Status]

from ODLN T0

  Inner Join DLN1 T1 on T0.DocEntry = T1.DocEntry

  Left Outer Join RDR1 T2 on T1.BaseType = 17 and T1.BaseEntry = T2.DocEntry and T1.BaseLine = T2.LineNum

  Left Outer Join ORDR T3 on T2.DocEntry = T3.DocEntry

  Left Outer Join INV1 T4 on T4.BaseType = 15 and T4.BaseEntry = T1.DocEntry and T4.BaseLine = T1.LineNum

  Left Outer Join OINV T5 on T4.DocEntry = T5.DocEntry

Where T0.DocDate>=[%0]

  and T0.DocDate<=[%1]

  and T1.OpenQty >0 or T1.TargetType=-1

Thanks

Unnikrishnan

[RahF]

Hi Kate

Try this

SELECT T0.[DocNum] as 'Delivery Docket',

T1.[BaseRef] as 'Sales Order',

T0.[CardCode] as 'Customer Code',

T0.[CardName] as 'Customer Name',

T0.[NumAtCard] as 'Cust Ord No',

T1.[ItemCode] as 'Stock Code',

T1.[Dscription] as 'Stock Description',

T1.[Quantity] as 'Qty',

T1.[ShipDate] as 'Shipped Date',

T1.[LineTotal] as 'Total'

FROM [dbo].[ODLN]  T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocStatus] = 'O' and T0.[DocDate]  >=  [%0] AND  T0.[DocDate]  <=  [%1]

GROUP BY T0.[DocNum], T1.[BaseRef], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[ShipDate], T1.[LineTotal]

ORDER BY T0.[DocNum], T0.[CardCode]

Thanks and Regards

Rahul