cancel
Showing results for 
Search instead for 
Did you mean: 

Query for open GRPO items as of a specific date.

divtech
Explorer
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

divtech
Explorer
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

Did you try my query?

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please try this query,

SELECT T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[LineStatus]  = 'o' and  T0.[DocDate] between [%0] and [%1]

Thanks

divtech
Explorer
0 Kudos

Hey Nagarajan,

Not sure what, but something is definitely off. The balance sheet (the purchase clearing acct) for that ending period and the query totals are way off :-/.

julie_jamieson2
Active Contributor
0 Kudos

The reconciliations are stored in ITR1 / OITR. You will need to look at this table to see if the transID in the journal has a reconciliation, the date of that reconciliation and the amount (to make sure it is fully reconciled)

something like this:

SELECT T0.[DocNum]

, T0.DocEntry

, T0.[DocStatus]

, T0.[DocDate]

, T0.[CardCode]

, T0.[CardName]

, T0.[NumAtCard]

, T1.[ItemCode]

, T1.[Dscription]

, T1.[Quantity]

, T1.[OpenQty]

FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[LineStatus]  = 'o' and  T0.[DocDate]< '[%0]'

and T0.DocNum NOT IN

(select T0.SrcObjAbs from ITR1 T0

INNER JOIN OITR T1 ON T0.ReconNum = T1.ReconNum where T0.SrcObjTyp = 20 and T1.ReconDate < [%0])

0 Kudos

Hi,

I tried your query, but it seems that it didn't capture the currency.

Is it possible to capture the local currency if I have multiple currencies?

Thanks and regards,

Oliver