Skip to Content
author's profile photo Former Member
Former Member

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

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 22, 2012 at 02:28 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 22, 2012 at 01:25 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 22, 2012 at 01:39 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 22, 2012 at 06:23 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.