SQL query to link OPOR (a/p purchase order) and ODPO (a/p down payment) in SAP B1

[former_member417651]

I can usually tease these out, but I'm having a hard time linking these two tables. What I ultimately need is PO info with the paidtodate from the a/p down payment invoice. I started to just go with Journal Entries, but was still having issues getting back to the PO.

[former_member1365233]

Can anyone help me out , i need a relation between OPOR and ODPO tables, as I want a query in which i can get a/p down payment data related to Purchase order.

Regards

Vinayak

[mgregur]

Hi,

try this:

SELECT *
FROM OPOR T0
INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN DPO1 T2 ON T1."ObjType" = T2."BaseType" AND T1."DocEntry" = T2."BaseEntry" AND T1."LineNum" = T2."BaseLine"
LEFT JOIN ODPO T3 ON T2."DocEntry" = T3."DocEntry"

Of course, change the SELECT part to fit your needs.

BR,

Matija