Dynamics GP – Using the “Batch Enquiry” window to assist troubleshooting the “Receiving” or “Busy” batch statuses

Introduction

Situations can arise where batches show a status of “Receiving” or “Busy” in the various series post windows. This is generally nothing to worry about, it just means a user is active with that batch, however if the batch continues to say “receiving” or “busy” when no one is posting, further investigation may be needed. When this happens users and admins will often dive straight into SQL to investigate further, however this post shows how you can use the little known “Batch Enquiry” window to get a greater insight into what is happening with batches in Dynamics GP, without the need for SQL. You can then take some remedial action to hopefully prevent any long winded SQL fixes.

The Busy and Receiving Batch Statuses

When you are working in a batch in Dynamics GP the batch status changes to record you are active in that batch. For example I’m working in the “GAV JAN INVS” batch so the series post window shows this batch as “Busy”

In the scenario below I’m posting some Sales Invoices, so the General Ledger Batch RMSLS000009 is showing a status of “Receiving”. (as this is receiving transactions from the Sales Invoice batch I’m posting)

As I mentioned above this is all normal behaviour, however instances can arise where no one is in the batch yet the batch status remains as “Busy” or “Receiving”.

Batch Enquiry

When this occurs users and admins can be tempted to delve straight into SQL however you can also use the “Batch Enquiry” window to see which users are active with those batches. The “Batch Enquiry” window is a little known window that gives you a peak into the “Batch Activity” table (the SY00800 table in the DYNAMICS database – a row is inserted into this table when a user is active in a batch).

You can access the window via the option “Enquiry > System > Batch”

Once in this window it gives you an overview of what users have which batches open and a status of what is happening in those batches.

Armed with this information we can check directly with those users to see if they are indeed working in those batches. If they aren’t then the first course of action is to ask them to log out of GP and log back in. Doing this can trigger GP to automatically recover the batch for you, negating the need for any intervention at SQL level.

For example the user “sa” wasn’t active with the batches “SL JAN INVS” or “RMSLS0000009” so after logging the “sa” user back into GP we were prompted with the message below:

This indicates that the system has automatically recovered the batch for you and now you can just go to “Batch Recovery” to continue the posting of the batch. There was no need to go into SQL and start manually removing activity records. In fact doing that in the first instance could have made the fix much more difficult. (i.e. if the batch activity record was removed manually in SQL its likely you wouldn’t have been prompted with the message to recover the batch. Therefore fixing things would have been a manual process)

Conclusion

Hopefully using the batch enquiry can help identify which users need to log out and back into GP which can sometimes force GP to fix any issues with the batch for you without the need for SQL intervention.

It can also help you monitor the system more effectively, giving an insight into which batches users are working on.

Thanks for reading!

Dynamics GP – Stranded records in PM20200 causing Invoices to go to History incorrectly when applying payments in Bank Management

The Issue

When a user was creating payables payments in the bank management module via “Transactions > Financial > Bank Management > Batches > Payments” they found that when they applied an invoice the invoice would go straight to HISTORY even though the payment hadn’t yet been posted.

As a consequence when the user did post the batch the payment hits the bank management module correctly, but then fails to post through the payables tables.

The error below is reported on the edit list and the payment remains in the WORK tables in payables, in a system generated batch prefixed CBPAY, and the invoices go to HISTORY with incomplete apply records:

This happened every time they applied a payment in the bank management module using the option mentioned above.

The root cause

On investigation I found stranded records in the PM20200 (PM Distribution OPEN OPEN Temporary File) table with the users ID in the KEYSOURC column causing the issue.

Therefore to fix the issue I just deleted these records and this prevented the issue. (always ensure no one is in the system and you have a full backup prior to deleting any data)

Fixing the aftermath

To fix the data so the payment could be posted and applied to the invoices, I had to first correct the payment and then transfer the invoices back to OPEN.

** Please note if you have encountered this issue your data fix may be slightly different. Therefore prior to running any DELETE or UPDATE statements first run SELECT statements to ensure you are deleting or updating the right data and above all always ensure you have a good backup of your data.

I first fixed the payment by running the following SQL statements to update the apply amounts:

UPDATE PM10400 SET APPLDAMT=0, CURTRXAM=DOCAMNT WHERE PMNTNMBR='<payment number>'

Next I transferred the invoice back to OPEN and removed the HISTORY records using the steps below:

--Insert into PM Open

INSERT INTO PM20000

(VCHRNMBR,VENDORID,DOCTYPE,DOCDATE,DOCNUMBR,DOCAMNT,CURTRXAM,DISTKNAM,DISCAMNT,DSCDLRAM,BACHNUMB,TRXSORCE,BCHSOURC,DISCDATE,DUEDATE,PORDNMBR,TEN99AMNT,WROFAMNT,DISAMTAV,TRXDSCRN,UN1099AM,BKTPURAM,BKTFRTAM,BKTMSCAM,VOIDED,HOLD,CHEKBKID,DINVPDOF,PPSAMDED,PPSTAXRT,PGRAMSBJ,GSTDSAMT,POSTEDDT,PTDUSRID,MODIFDT,MDFUSRID,PYENTTYP,CARDNAME,PRCHAMNT,TRDISAMT,MSCCHAMT,FRTAMNT,TAXAMNT,TTLPYMTS,CURNCYID,PYMTRMID,SHIPMTHD,TAXSCHID,PCHSCHID,FRTSCHID,MSCSCHID,PSTGDATE,DISAVTKN,CNTRLTYP,NOTEINDX,PRCTDISC,RETNAGAM,ICTRX,Tax_Date,PRCHDATE,CORRCTN,SIMPLIFD,BNKRCAMT,APLYWITH,Electronic,ECTRX,DocPrinted,TaxInvReqd,VNDCHKNM,BackoutTradeDisc,CBVAT,VADCDTRO,TEN99TYPE,TEN99BOXNUMBER)

SELECT

VCHRNMBR,VENDORID,DOCTYPE,DOCDATE,DOCNUMBR,DOCAMNT,CURTRXAM,DISTKNAM,DISCAMNT,DSCDLRAM,BACHNUMB,TRXSORCE,BCHSOURC,DISCDATE,DUEDATE,PORDNMBR,TEN99AMNT,WROFAMNT,DISAMTAV,TRXDSCRN,UN1099AM,BKTPURAM,BKTFRTAM,BKTMSCAM,VOIDED,HOLD,CHEKBKID,DINVPDOF,PPSAMDED,PPSTAXRT,PGRAMSBJ,GSTDSAMT,POSTEDDT,PTDUSRID,MODIFDT,MDFUSRID,PYENTTYP,CARDNAME,PRCHAMNT,TRDISAMT,MSCCHAMT,FRTAMNT,TAXAMNT,TTLPYMTS,CURNCYID,PYMTRMID,SHIPMTHD,TAXSCHID,PCHSCHID,FRTSCHID,MSCSCHID,PSTGDATE,DISAVTKN,CNTRLTYP,NOTEINDX,PRCTDISC,RETNAGAM,ICTRX,Tax_Date,PRCHDATE,CORRCTN,SIMPLIFD,0,APLYWITH,Electronic,ECTRX,DocPrinted,TaxInvReqd,VNDCHKNM,BackoutTradeDisc,CBVAT,VADCDTRO,TEN99TYPE,TEN99BOXNUMBER

FROM PM30200 WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')

--Delete from History Table

DELETE FROM pm30200 WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')

--Update Current Transaction Amount

UPDATE PM20000 set CURTRXAM= DOCAMNT WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')



--Insert Distributions back into Open

INSERT INTO PM10100

(VCHRNMBR,DSTSQNUM,CNTRLTYP,CRDTAMNT,DEBITAMT,DSTINDX,DISTTYPE,CHANGED,USERID,PSTGSTUS,VENDORID,TRXSORCE,PSTGDATE,INTERID,CURNCYID,CURRNIDX,ORCRDAMT,ORDBTAMT,APTVCHNM,APTODCTY,SPCLDIST,DistRef,RATETPID,EXGTBLID,XCHGRATE,EXCHDATE,TIME1,RTCLCMTD,DECPLACS,EXPNDATE,ICCURRID,ICCURRIX,DENXRATE,MCTRXSTT,CorrespondingUnit)

SELECT

VCHRNMBR,DSTSQNUM,CNTRLTYP,CRDTAMNT,DEBITAMT,DSTINDX,DISTTYPE,CHANGED,USERID,PSTGSTUS,VENDORID,TRXSORCE,PSTGDATE,'ACAL2',CURNCYID,CURRNIDX,ORCRDAMT,ORDBTAMT,APTVCHNM,APTODCTY,SPCLDIST,DistRef,'','',1,'1900-01-01 00:00:00.000','1900-01-01 00:00:00.000',0,2,'1900-01-01 00:00:00.000','GBP',1014,0,0,''

FROM PM30600 WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')

--Delete dists from hist

DELETE FROM PM30600 WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')

--Update Key Record

UPDATE PM00400 set DCSTATUS=2 where CNTRLNUM IN ('<Invoice Voucher Numbers>')

-- Insert tax records back to PM10500

INSERT INTO pm10500
(VENDORID,VCHRNMBR,DOCTYPE,BACHNUMB,TAXDTLID,BKOUTTAX,TAXAMNT,ORTAXAMT,PCTAXAMT,ORPURTAX,FRTTXAMT,ORFRTTAX,MSCTXAMT,ORMSCTAX,ACTINDX,TRXSORCE,TDTTXPUR,ORTXBPUR,TXDTTPUR,ORTOTPUR,CURRNIDX,POSTED)

SELECT
VENDORID,VCHRNMBR,DOCTYPE,BACHNUMB,TAXDTLID,BKOUTTAX,TAXAMNT,ORTAXAMT,PCTAXAMT,ORPURTAX,FRTTXAMT,ORFRTTAX,MSCTXAMT,ORMSCTAX,ACTINDX,TRXSORCE,TDTTXPUR,ORTXBPUR,TXDTTPUR,ORTOTPUR,CURRNIDX,1
FROM PM30700 WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')

--Delete hist tax records from pm30700

DELETE PM30700 WHERE VCHRNMBR IN ('<Invoice Voucher Numbers>')

-- Delete History Applies

DELETE FROM PM30300 WHERE APTVCHNM IN ('<Invoice Voucher Numbers>')

Once I had completed these steps I could post the payment and apply to the invoices.

Thanks for reading!

Dynamics GP – What are the P/L Source Document entries on the year end journal?

When you run the year end routine on the General Ledger in Dynamics GP the system automatically creates a journal to bring forward the balances for the Balance Sheet codes and it also closes all the profit and loss codes to the retained earnings account.

When you look at the header of the year end journal that’s created it shows a source document of BBF (Balance Brought Forward)

However if you take a closer look at the year end journal in smartlist you can see that some of the lines have a source document of “P/L”.

So why the mixture of BBF and P/L source documents for the year end journal?

The BBF entries are created to bring forward the balances of the “Balance Sheet” accounts and the “P/L” entries are created by closing the profit and loss accounts to the retained earnings account. As my demo data has multi currency postings to various profit and loss codes I get one entry per currency.

To confirm the correct amounts have been posted to the retained earnings account I can run the query below in SQL that sums all the amounts posted to the profit and loss accounts for the year 2024, and this reconciles back to the amounts posted to the retained earnings account on the year end journal:

The BBF entries are quite well known but I couldn’t find much information on the P/L source document which is why I created this post.

Thanks for reading!