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?

  • We have an Excel Powerpivot query to use for this. Happy to share.

  • Hi 

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

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

  • 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