Purchase order vs AP invoice

[Former Member]

Hi, we are on SAP B1 9.2 patch 3, I require a report which can detail how many purchase orders has been raised on a particular project and how much purchase has been booked against POs. I have made a query but output is not sufficient as PO amount is getting repeated, requested to help

SELECT distinct T0."DocType", T0."DocStatus", T0."CardName",T0."DocNum" as " SKV Purchase Order", T0."DocTotal",T3."NumAtCard" as "Vendor Invoice No", T3."DocDate" as " Invoice Date", T3."DocTotal" as " Invoiced Amount", T1."Project" FROM Opor T0 INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry" Left join PCH1 T2 on T2."BaseEntry" = T0."DocEntry"Left JOIN OPCH T3 ON T2."DocEntry" = T3."DocEntry"left JOIN OVPM T4 ON T3."ReceiptNum" = T4."DocEntry" LEFT JOIN DPO1 T5 ON T5."BaseEntry" = T0."DocEntry" left join ODPO T6 on T5."DocEntry" = T6."DocEntry" left JOIN OVPM T7 ON T6."ReceiptNum" = T7."DocEntry" Where T1."Project" = [%0]