on 09-12-2016 11:26 PM
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
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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])
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.