Query for open GRPO items as of a specific date.

[divtech]

Hi All,

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 :-).

Thanks,

Jaz

[divtech]

Let me see if I can make this request a little simpler.

Today is 09-15-2016. I need to see GRPOs that had an 'Open' status ON 12/31/2015. If I can get that then I can add the other requirements such as line total...open total....etc afterwards.

Thanks!

[Former Member]

Hi Jaz,

Just try this!!!

SELECT T0.[DocDate],T0.[DocEntry],T0.[DocNum], T2.[ItmsGrpCod],T3.[ItmsGrpNam],  T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal], T0.[NumAtCard] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod Where T0.[DocDate]>=[%0]

Regards

Raghu

[julie_jamieson2]

Did you try my query?