CODEWITHSUNDEEP
sqlsql-servert-sqlhana
Redwan Agharoud
Redwan Agharoud

Reputation: 27

JOIN Between OPOR (Purchase orders) and ORDR (Sales orders) (SAP Business One - SQL Server)

I am currently working in SAP Business One and came across the following problem:

I can't connect the purchase to sales orders. I used the OITM Table to make a connection between the two.

I Tried the following:

SELECT T0."DocNum", T0."CardName", T0."NumAtCard", T0."DocDate", T0."ReqDate", T0."U_ACM_ORDERINFO", T4."DocNum", T4."CardName", T4."U_ACM_CONTAINERLOAD", T4."U_ACM_O_ETD", T4."U_ACM_A_ETD" 
FROM ORDR T0 
INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" 
INNER JOIN POR1 T3 ON T2."ItemCode" = T3."ItemCode" 
INNER JOIN OPOR T4 ON T3."DocEntry" = T4."DocEntry"

Unfortunatly, when executing this statement, the outcome is not unique. enter image description here

Can someone help me in making a good connection between the purchase and sales orders and also make the lines unique

The outcome would be something like this

enter image description here

Thank you in advance!

Upvotes: 1

Views: 2894

Answers (2)

Egil Hansen
Egil Hansen

Reputation: 170

I just had a similar issue, so i thought i'd share my findings for others to come.

The connection/link between sales orders and purchase orders is done from RDR1.PoTrgEntry = OPOR.DocEntry.

Full example:

SELECT T0.DocNum 'Sales order',
 T1.LineNum, T1.ItemCode, T1.Quantity, T1.ShipDate,
 T2.DocNum 'Purchase order', T2.CardCode, T2.CardName
 FROM ORDR T0  
 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
 INNER JOIN OPOR T2 On T2.DocEntry = T1.PoTrgEntry

Upvotes: 0

asantz96
asantz96

Reputation: 619

There are not a relationship between the purchases and the sales order.

Normally, the purchase code is not the same as the sales code, I do not know if you have count with it.

You can use two diferents queries and join it for know the amount of the sales and the amout of the purchase quantity in a concrete time. However, I do not think that you want this.

Upvotes: 3

Related Questions