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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s