Skip to Content

Show the PO Number Document related to a Delivery Batch on SAP B1

I am trying to build a query that will show the batches and their corresponding source goods receipt and purchase order for each delivery document. This is because each PO has a link to a quality document that I want to easily access.

I have crafted the query below which is two separate queries joined together. The first listing delivery batches for a specific delivery document and the second that is meant to take that batch number and find the corresponding PO. These work separately, but not together.

Any help would be appreciated.

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T4.[DistNumber],T7.[DocNum] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry left join  OITL T2 on t1.docentry = T2.[ApplyEntry] and  T1.[LineNum]  = T2.[ApplyLine] and T2.[ApplyType] = 15 INNER JOIN ITL1 T3 ON T2.LogEntry = T3.LogEntry left join  OBTN T4 on T4.[ItemCode]  =  T3.[ItemCode]  and T3.[MdAbsEntry]  =  t4.[absentry] LEFT JOIN ITL1 T5 ON T4.AbsEntry = T5.MdAbsEntry INNER JOIN OITL T6 ON T5.[LogEntry] = T6.LogEntry AND T6.ApplyType = 20 INNER JOIN OPOR T7 ON T6.[BaseEntry]=T7.[DocEntry] WHERE T0.Docnum = [%0] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T4.[DistNumber],T7.[DocNum]
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 15, 2017 at 11:54 AM

    Hi,

    Did you try standard Batch transaction report from SAP B1?

    In SAP B1, no direct link between PO and DO. Also there is no batch invovlement in PO.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • May 15, 2017 at 01:39 PM

    That's not very helpful. I need to be able to present the related PO number and provide a direct link as part of an analysis report. In particular we have a quality info on the PO that needs to be displayed along with a delivery. Once I can link to the PO, I can get to that info. It needs to be flexible enough to work for both a drop ship and a batch delivery.

    Add comment
    10|10000 characters needed characters exceeded