Create a query on open GRPO at a particular point in time?

[Former Member]

Hi all,

I'm pretty sure this is not possible but could someone please confirm.

Is it possible to create an open document query for Good Receipt PO at a particular point of time?  I've tried using OPDN table with ADOC but I do not see anthing there.  And Open items list only give what is open from today's date.

So for example say on 3rd july we have 10 open GRPO documents the query should return 10, but if changing the date of the query to a later date say 12th July when there is only 3 now open GRPO the query will then return 3.

Is this possible?

Many thanks in advance,

Brian

[Former Member · Accepted solution]

Hi Brian,

It is highly possible.

You can try start here:

SELECT T0.DocNum, MAX(T0.UpdateDate) 'Closing Date' FROM ADOC T0

WHERE T0.ObjType = '20' AND T0.LogInstanc

=(SELECT MIN(LogInstanc) FROM ADOC WHERE

DocStatus='C' AND DocNum=T0.DocNum AND LogInstanc=T0.LogInstanc

GROUP BY DocNum)

GROUP BY T0.DocNum

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

thanks but sorry don't quite follow this query!  When I plug this query in my system it returns noting even if I know for sure there are open GRPO's documents!  Can you please advise or am I doing this incorrectly?

Many thanks,

Brian

[Former Member · Accepted solution]

If the query return nothing, it means your log file may not be efficient. How many instances do you keep?

Run a simple query:

SELECT count(*) FROM ADOC T0

WHERE T0.ObjType = '20'

What is the result?

[Former Member · Accepted solution]

The result is just 1.

Brian

[Former Member · Accepted solution]

Check what happened for your log by: SELECT count(*) FROM ADOC

[Former Member · Accepted solution]

That query returned the number 37. Is this correct?

[Former Member · Accepted solution]

No. It is not right. It should be much much more. How long is your B1 history?

[Former Member · Accepted solution]

Pretty long.  Data goes back to 2006 and the GRPO that are currently open I have about 29 on my system.

[Former Member · Accepted solution]

Hi Brian..........

It can be worked out.

But there is some long way get the Document Open/Closed at a given point in time.

Anyhow if you can get the data of AP Invoice when exactly made then you may get the status of OPDN.

Let say if on 10th GRPO is open and you run it on 10th it will show you open. But if on 12th AP invoice is made then obviously you can get the Doc Status of GRPO is closed. SO you can identify the Doc Status through AP Invoice date.........

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul,

Thanks I did consider this option but what if the GRPO was returned or created AP invoice on the same date?

Hi Gordon,

anymore ideas on creating this query by chance?

Brian

[Former Member · Accepted solution]

If you do not keep any log, there is no way to get the result you want. Fix the log problem is more important than this query. Otherwise, your system will be no audit trail for anything happened.

[Former Member]

Check you log setting. It must be stopped working for quite a long time.

If possible, check any previous backup to find when did the problem start.

[Johan_H]

Hi Gordon,

We started our system December 2003, and the reason I thought it wasn't possible was because in our system your solution does not give reliable results either.

My guess is that  the B1 system did not maintain the UpdateDate field correctly / reliably in older versions.

In newer installations (perhaps 2007 and up ?), I am sure it would work nicely.

Regards,

Johan

[KennedyT21]

Hi Brian,

Yes , You are correct,

But check in the reverse process when did the order closed by checking the AP Invoice, G.return,Incoming Excise Invoice and count based upon that.

Or Using SDK you can post the actual close date in GRPO in the UDF and achieve this

Regards

Kennedy

[Johan_H]

Hi Brian,

There is no reliable way to get that information, because DocStatus changes by date, not by time. So a GRPO that was created and closed on the same day, would show up as closed in all entries in the history table.

Your question is not uncommon though. You just need to approach it differently. The goods receipt was either closed by a Purchase Invoice or reconciled some other way. Use for example the BaseEntry and BaseType fields to determine the invoice document with which the GPRO was closed and compare their dates.

Regards,

Johan