Skip to Content
0
Former Member
Dec 22, 2011 at 12:29 PM

query to display sales invoice number by sales order number

1378 Views

Hi,

Can someone please guide me for tracking sales invoice number using sales order number (a query only for quick info)?

A good to have would be to see the difference between the sales order and invoice document (mainly the quantity of each product).

Usually the sale order is copied to delivery (with optional returns) and after that to invoice (with optional returns)..

I've found a partial solution here: Link: [http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/17226] [original link is broken] [original link is broken]; but I have a problem with parameter in SAP B1 and its not exactly what I need..

Thank you in advance.

So basically a complicated extension to simple query (union?):

SELECT T0.[DocNum], T1.[ItemCode], T1.[Quantity], T1.[Price] 
FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
WHERE T0.[DocNum] = [%0]

with additional columns like:

Quantity inv (invoice quantity considering corrections),

Price inv (invoice price considering corretions),

Quantity final (difference = Quantity inv - order),

Price final (difference = Price inv - order)

It would be great for the query to omit products with itemcode starting with 0.

Edited by: Tomasz Bolesta on Dec 22, 2011 2:05 PM