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

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

Thanks!  I'll give it a go.

Cheers,

Ric

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

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

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