Skip to Content
0

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

May 15, 2017 at 11:44 AM

87

avatar image

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]
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Nagarajan K May 15, 2017 at 11:54 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Russell Kallman May 15, 2017 at 01:39 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded