I am trying to generate a query within SAP B1 (9.2 if it matters) that will allow me to see all open GRPOs (by line item). I only need to see GRPO line items that are still open for the date that I specify. The line items should also show total open quantity as well. I would also like a total at the end of the query. I came up with the following but I am going at it from the wrong direction...
SELECT T0.[CardCode], T0.[CardName], T1.[DocNum], T1.[TaxDate], T2.[ItemCode], T2.[Quantity], T2.[LineNum], T2.[LineTotal] FROM OCRD T0 INNER JOIN OPDN T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN PDN1 T2 ON T1.[DocEntry] = T2.[DocEntry] WHERE (T1.DocDate>='[%0]' and T1.DocDate<='[%1]') AND DocStatus = 'O'
New to SAP and SQL so any input is appreciated :-).