Skip to Content
avatar image
Former Member

SAP B1 Delivery Query

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Sep 14, 2017 at 08:13 AM

    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

    Add comment
    10|10000 characters needed characters exceeded