SAP B1 Delivery Query

[Former Member]

Hello,

I Currently have the below query:

SELECT t0.Cardcode as "Customer#", t0.Cardname as "Customer Name", t0.DocNum as "SO #", t1.ItemCode, t0.DocDate, t0.DocDueDate, t1.OpenQty, t1.OpenQty*T1.price AS 'Open Value' FROM dbo.ORDR t0 INNER JOIN dbo.RDR1 t1 on t1.docentry = t0.docentry WHERE t1.LineStatus = 'O' AND t1.OpenQty > 0

I would like to remove the end column and replace with a two columns that show Current Stock for that item and a column for when the next Purchase Order for that good is due?

Much appreciate your help.

[Johan_H]

Hi James,

Assuming that you always have only one open purchase order for any item, try this:

SELECT t0.Cardcode as [Customer#]
     , t0.Cardname as [Customer Name]
     , t0.DocNum as [SO #]
     , t1.ItemCode
     , t0.DocDate
     , t0.DocDueDate
     , t1.OpenQty
     , t1.OpenQty*T1.price AS [Open Value]
     , ISNULL((select max(DocDueDate) 
               from OPOR
                    inner join POR1 on OPOR.DocEntry = POR1.DocEntry
                                   and POR1.ItemCode = t1.ItemCode)
             , DATEADD(YEAR, 99, GETDATE())) AS [Next PO due]
FROM ORDR t0 
     INNER JOIN RDR1 t1 on t1.docentry = t0.docentry 
WHERE t1.LineStatus = 'O' 
  AND t1.OpenQty > 0

I will leave it to you to remove any columns that you do not need.

Regards,

Johan