Purchase Order Clearing Account Reconciliation

Using Sage 300 Premium 2018, with Purchasing and Inventory Control modules.

The PO Clearing account on the balance sheet has no means of reconciliation or audit report that proves the GL.

I need a report that shows PO receipts that reconciles to the balance of the GL.

Any suggestions?

Parents Reply Children
  • in reply to Andrew789

    Hi 

    We also dealing with the same issue. Are you willing to share your PP query with me?

  • in reply to Andrew789

    Sorry - I must have missed your response.  Yes - can you share?

  • in reply to Ric S

    We have 2 reports set up:
    1. Goods received not yet invoiced (GRNI)
    2. Goods returned not yet credited (GRNC)

    First, go to I/C Account sets and note the General Ledger (GL) code for Payables Clearing. We only use one Payables Clearing account across each of our 3 different I/C Account sets.
    Second, find out from your IT team the server name and database name for your Sage 300 database.
    Third, check G/L Accounts for the GL configuration of your Payables Clearing GL account. Ideally the relevant GL account should be specified as a Control Account.

    In the following code:
    > substitute your Database name for ZZZZZZ
    > substitute your Payables Clearing GL account for XXXXXX

    Goods received not yet invoiced (GRNI) SQL query:

    SELECT
    "GLPOST"."ACCTID", "GLPOST"."FISCALYR", "GLPOST"."FISCALPERD", "GLPOST"."SRCELEDGER", "GLPOST"."JRNLDATE", "GLPOST"."BATCHNBR", "GLPOST"."ENTRYNBR", "GLPOST"."JNLDTLDESC", "GLPOST"."JNLDTLREF", "GLPOST"."TRANSAMT", "PORCPH1"."RCPNUMBER", "PORCPH1"."ISINVOICED", "PORCPH1"."PONUMBER"

    FROM  
    ("ZZZZZZ"."dbo"."GLPOST" "GLPOST"
    INNER JOIN "ZZZZZZ"."dbo"."GLJEH" "GLJEH" ON (("GLPOST"."DRILSRCTY"="GLJEH"."DRILSRCTY") AND ("GLPOST"."DRILLDWNLK"="GLJEH"."DRILLDWNLK") AND "GLPOST"."DRILAPP"="GLJEH"."DRILAPP"))
    INNER JOIN "ZZZZZZ"."dbo"."PORCPH1" "PORCPH1" ON "GLJEH"."DRILLDWNLK"="PORCPH1"."RCPHSEQ"
    WHERE 
    "PORCPH1"."ISINVOICED"=0 AND "GLPOST"."SRCELEDGER"='PO' AND "GLPOST"."ACCTID"='XXXXXX' AND "GLJEH"."ERRBATCH"=0

    Goods returned not yet credited (GRNC) SQL query:

    SELECT
    "GLPOST"."ACCTID", "GLPOST"."FISCALYR", "GLPOST"."FISCALPERD", "GLPOST"."SRCELEDGER", "GLPOST"."JRNLDATE", "GLPOST"."BATCHNBR", "GLPOST"."ENTRYNBR", "GLPOST"."JNLDTLDESC", "GLPOST"."JNLDTLREF", "GLPOST"."TRANSAMT", "PORETH1"."RETNUMBER", "PORETH1"."ISCREDITED", "PORETH1"."PONUMBER"
    FROM  
    ("ZZZZZZ"."dbo"."GLPOST" "GLPOST"
    INNER JOIN "ZZZZZZ"."dbo"."GLJEH" "GLJEH" ON (("GLPOST"."DRILSRCTY"="GLJEH"."DRILSRCTY") AND ("GLPOST"."DRILLDWNLK"="GLJEH"."DRILLDWNLK") AND "GLPOST"."DRILAPP"="GLJEH"."DRILAPP"))
    INNER JOIN "ZZZZZZ"."dbo"."PORETH1" "PORETH1" ON "GLJEH"."DRILLDWNLK"="PORETH1"."RETHSEQ"
    WHERE 
    "PORETH1"."ISCREDITED"=0 AND "GLPOST"."SRCELEDGER"='PO' AND "GLPOST"."ACCTID"='XXXXXX' AND "GLJEH"."ERRBATCH"=0

  • in reply to Neels van der Walt

    See reply to Ric S 

  • in reply to Andrew789

    Thanks!  I'll give it a go.

    Cheers,

    Ric

  • in reply to Andrew789

    this was an incredibly helpful query.  Thanks so much.   Have you ever adapted this to assist clients to reconcile their clearing account balance as at a certain date.  Like at year end.  This works great as of the current date but if we were to cut off at a certain date could we identify what is in the clearing?  Ever done anything like that

  • in reply to ValerieH

    We haven't tried to adapt the SQL queries to enable getting a snapshot at a past date.  Would be an interesting exercise though!