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!

Dynamics GP – Error message “Unhandled script exception: SCRIPTS – data area” opening the Payables Enquiry window

I’m come across this error a few times so thought I’d document on my blog.

The user is presented with the below error when opening the “Enquiry > Purchasing > Transaction by Document” window in Dynamics GP

When the window does open there’s a blank record showing in the scrolling window:

When you expand the window and attempt to select it you are prompted with the message “The selected record has been deleted by another user” and it disappears from the window

In this particular instance the issue is being caused by an incorrect PM Keys Record in the PM00400 table.

If I search for an incomplete record in the PM00400 table using the SQL query below this returns one record:

SELECT * FROM PM00400 
WHERE VENDORID='' AND DCSTATUS=3 AND DOCNUMBR='' AND DOCTYPE=0

If I then remove this record using the DELETE statement below the error no longer occurs when I open the window

* Always ensure you have adequate backups prior to deleting data from SQL

DELETE FROM PM00400 
WHERE VENDORID='' AND DCSTATUS=3 AND DOCNUMBR='' AND DOCTYPE=0

Please note you could also delete the entire contents of the PM00400 table and have the system recreate it using checklinks however this isn’t practical in my case.

I hope this helps anyone who encounters this issue in future.

Thanks for reading!