Dynamics GP Fully applied payments stuck in Open

I was on the Microsoft forums recently helping to resolve the above issue for a partner in the UK. Ordinarily fully applied payables documents automatically move to history, however every now and then they don’t and they need a little nudge.

During the course of this forum post the standard process for forcing this move did not work and Jon (another Touchstone consultant) was able to offer other alternatives and I was also able to dig out an old script from our sql archives from version 6 days (may need the odd tweak for 2010).

As such a wealth of advice came out I thought it was worth documenting hear.

Standard Process;

The standard process is to rebuild the PM master keys (Delete PM00400) and the run checklinks on the payables transaction and transaction history logical groups to rebuild the PM00400 table. It is worth noting that you can lose some drill down ability doing this. Make sure you do a back up first.

This should do the job 9 times out of 10.

Remittance process:

Jon (Our Tame Dex Developer) has discovered that in some cases the transactions do not move if you have records in the PM20100 and they have a KEYSOURC of REMITTANCE. The assumption is that checklinks wont move them until the remittance is printed.

Jon has found that if you delete the records from the PM20100 and then run the standard process the records move.

The Move routine:

Jon has also found that you can run the move routine indervidually with the following SQL code.

declare @FuncCurr varchar(15)

declare @ErrorState int

select @FuncCurr = FUNLCURR from MC40000

create table #CNTRLNUMTEMP

(

CNTRLNUM VARCHAR(21) NOT NULL,

DOCTYPE SMALLINT NOT NULL,

VENDORID VARCHAR(15) NOT NULL

)

exec pmclmovefullyapplied

'sa', @FuncCurr, 'PM_Transaction_OPEN', '%1', @ErrorState output

select * from SY03400

delete SY03400

SQL Script to move open to history:

If all of this fails i have in my archive a SQL script to move open to History. This was written for version 6 originally so may need some tweaking for 2010.



/*

** Procedure Name: PM move open to history for R6

**

** Description:

**

** Script is for R6 PM transactions that show fully applied and are still in the open table.

**

** Important:

** There are numerous places where the Voucher Number, Document Type, Control Number, or Vendor ID

** must be provided. To find these locations do a search on 00000000000000001. Replace 00000000000000001

** with the voucher number you want to move to history.

** Also do a search on '??????????' and replace this with the Vendor Id of the voucher.

** If the invoice needs to be moved then change DOCTYPE to 1 and CTRLTYP to 0.

** If the payment needs to be moved then change DOCTYPE to 6 and CTRLTYP to 1.

** For the Distribution section, the History has a DOCTYPE but the Work does not. The script is

** hardcoded for invoices. If the document is a payment, in the line following the 'select' in

** PM30600's insert statement, replace the 1 with 6.

**

** This script does not update reprint information (the PM80000's tables).

**

**

** Database:

**

** Any

**

**

** Tables:

**

** SQL Table Access Method

** --------------------- -------------

** PM30200 Read/Write

** PM20000 Read/Write

** PM00400 Read

** PM30300 Read/Write

** PM10200 Read/Write

** PM30600 Read/Write

** PM10100 Read/Write

** PM10500 Read/Write

** PM30700 Read/Write

**

**

******************************************************************************************

*/



begin

insert into PM30200 (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,APLYWITH,Electronic,ECTRX,

DocPrinted,TaxInvReqd,VNDCHKNM)

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,APLYWITH,Electronic,ECTRX,

DocPrinted,TaxInvReqd,VNDCHKNM

from PM20000

where DOCTYPE = 1

and VCHRNMBR = '00000000000000001'

and VENDORID = '??????????'



delete PM20000

where VCHRNMBR = '00000000000000001'

and DOCTYPE = 1

and VENDORID = '??????????'



print 'Your insert into PM30200 is complete. Now continue with update of the PM00400 table.'





/*This script will update the PM00400 table and flag the document from Open to History */

/*The CTRLNUM is the voucher number. The CNTRLTYP is 0 for invoice and 1 for payment */



update PM00400 set DCSTATUS = 3

where CNTRLNUM = '00000000000000001' and VENDORID= '??????????' and DCSTATUS = 2 and CNTRLTYP = 0



print 'Your update on PM00400 is complete. Now continue with insert on the PM30300 table'

end





/* This script will move the apply records from Apply to Work Open to Apply to History and then

** delete the PM10200 records. The apply to Voucher Number must be provided, which is the

** invoice voucher number.

*/



begin

insert PM30300

(VENDORID,DOCDATE,DATE1,GLPOSTDT,TIME1,VCHRNMBR,

DOCTYPE,APFRDCNM,ApplyFromGLPostDate,FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN,

APFRMDISCAVAIL,APFRMWROFAMT,ActualApplyToAmount,ActualDiscTakenAmount,ActualDiscAvailTaken, ActualWriteOffAmount,

APFRMEXRATE,APFRMDENRATE,APFRMRTCLCMETH,APFRMMCTRXSTT,APTVCHNM, APTODCTY,

APTODCNM,APTODCDT,ApplyToGLPostDate,CURNCYID,CURRNIDX,APPLDAMT,

DISTKNAM,DISAVTKN,WROFAMNT,ORAPPAMT,ORDISTKN, ORDATKN,

ORWROFAM,APTOEXRATE,APTODENRATE,APTORTCLCMETH,APTOMCTRXSTT,PPSAMDED,

GSTDSAMT,TAXDTLID,POSTED,TEN99AMNT,RLGANLOS,APYFRMRNDAMT,

APYTORNDAMT,APYTORNDDISC,OAPYFRMRNDAMT,OAPYTORNDAMT,OAPYTORNDDISC,Settled_Gain_CreditCurrT,

Settled_Loss_CreditCurrT,Settled_Gain_DebitCurrTr,Settled_Loss_DebitCurrTr,Settled_Gain_DebitDiscAv,

Settled_Loss_DebitDiscAv)

select

VENDORID,DOCDATE,DATE1,GLPOSTDT,TIME1,VCHRNMBR,

DOCTYPE,APFRDCNM,ApplyFromGLPostDate,FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN,

APFRMDISCAVAIL,APFRMWROFAMT,ActualApplyToAmount,ActualDiscTakenAmount,ActualDiscAvailTaken, ActualWriteOffAmount,

APFRMEXRATE,APFRMDENRATE,APFRMRTCLCMETH,APFRMMCTRXSTT,APTVCHNM, APTODCTY,

APTODCNM,APTODCDT,ApplyToGLPostDate,CURNCYID,CURRNIDX,APPLDAMT,

DISTKNAM,DISAVTKN,WROFAMNT,ORAPPAMT,ORDISTKN, ORDATKN,

ORWROFAM,APTOEXRATE,APTODENRATE,APTORTCLCMETH,APTOMCTRXSTT,PPSAMDED,

GSTDSAMT,TAXDTLID,POSTED,TEN99AMNT,RLGANLOS,APYFRMRNDAMT,

APYTORNDAMT,APYTORNDDISC,OAPYFRMRNDAMT,OAPYTORNDAMT,OAPYTORNDDISC,Settled_Gain_CreditCurrT,

Settled_Loss_CreditCurrT,Settled_Gain_DebitCurrTr,Settled_Loss_DebitCurrTr,Settled_Gain_DebitDiscAv,

Settled_Loss_DebitDiscAv

from PM10200

where APTVCHNM = '00000000000000001'

and APTODCTY = 1

and VENDORID = '??????????'



delete PM10200

where APTVCHNM = '00000000000000001'

and APTODCTY = 1

and VENDORID = '??????????'



print 'Your update on PM30300 is complete. Now continuing with insert on the PM30600 table'

end





/* This script will move the distribution records from the Distribution Work Open to Hist and

** then deletes the PM10100 records. The Voucher Number must be provided. The CNTRLTYP is 0

** for invoice and 1 for payment.

*/



begin

insert into PM30600

(DOCTYPE,VCHRNMBR,DSTSQNUM,CNTRLTYP,CRDTAMNT,DEBITAMT,

DSTINDX,DISTTYPE,CHANGED,USERID,PSTGSTUS,VENDORID,

TRXSORCE,PSTGDATE,CURNCYID,CURRNIDX,ORCRDAMT,ORDBTAMT,

APTVCHNM,APTODCTY,SPCLDIST,DistRef)

select

1,VCHRNMBR,DSTSQNUM,CNTRLTYP,CRDTAMNT,DEBITAMT,

DSTINDX,DISTTYPE,CHANGED,USERID,PSTGSTUS,VENDORID,

TRXSORCE,PSTGDATE,CURNCYID,CURRNIDX,ORCRDAMT,ORDBTAMT,

APTVCHNM,APTODCTY,SPCLDIST,DistRef

from

PM10100

where VCHRNMBR = '00000000000000001'

and VENDORID = '??????????'

and CNTRLTYP = 0



delete PM10100

where VCHRNMBR = '00000000000000001'

and VENDORID = '??????????'

and CNTRLTYP = 0





print 'Your update on PM30600 is complete. Now continuing with insert on the PM30700 table'

end





/* This script will move the Tax records from Tax Work to Tax History and then deletes the

** PM10500 records. The apply to Voucher Number must be provided.

*/



begin

insert PM30700

(VENDORID,VCHRNMBR,DOCTYPE,BACHNUMB,TAXDTLID,BKOUTTAX,

TAXAMNT,ORTAXAMT,PCTAXAMT,ORPURTAX,FRTTXAMT,ORFRTTAX,

MSCTXAMT,ORMSCTAX,ACTINDX,TRXSORCE,TDTTXPUR,ORTXBPUR,

TXDTTPUR,ORTOTPUR,CURRNIDX)

select

VENDORID,VCHRNMBR,DOCTYPE,BACHNUMB,TAXDTLID,BKOUTTAX,

TAXAMNT,ORTAXAMT,PCTAXAMT,ORPURTAX,FRTTXAMT,ORFRTTAX,

MSCTXAMT,ORMSCTAX,ACTINDX,TRXSORCE,TDTTXPUR,ORTXBPUR,

TXDTTPUR,ORTOTPUR,CURRNIDX

from PM10500

where VCHRNMBR = '00000000000000001'

and DOCTYPE = 1

and VENDORID = '??????????'



delete PM10500

where VCHRNMBR = '00000000000000001'

and DOCTYPE = 1

and VENDORID = '??????????'



print 'Your update on PM30700 is complete. Move process is complete.'

end