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

Dynamics GP and switching it off and on again (that fixes everything..doesn’t it?)

I got a joke email this morning entitled “Interesting Statistics”. One of the statistics was the likelihood of a computer issue being solved by switching it off and on again. I smiled at this thinking “how true” and then I realised that this can be a potentially problematic attitude to have with Dynamics GP, after all we are dealing with transactional data which does change status and transfer between tables on a regular basis. An interruption to this process can cause issues.


So here is my guide to switching it off and on again.

First things first, is it still doing something?

• Check the process monitor, Microsoft Dynamics GP button and process monitor. Here you can see if there are any processes queued up on your machine. You can take an informed decision as to whether that process is transactional in nature. Some processes can be suspended or even removed. Note the system will not allow you to remove certain processes.

• Check the Task manager on your work station. See if there are other processes running that can free up memory, make sure you are not maxed out on CPU usage. Check the amount of available ram as opposed to page file usage. Page files are actually on your hard disk as opposed to ram (on a chip) and so are naturally slower.

If you really have no choice, crash out.

This is not as drastic as it seems. Since Dynamics GP version 6 we have had batch recovery, this will catch a lot of interruptions etc, and allow you to continue posting. You should get a nice friendly message when you re enter Dynamics GP that there is a batch awaiting you in batch recovery.

Also remember if you crash out your user will be locked in. In versions 9 and above of Dynamics GP you will be able to delete your own activity at login and log back in , if you are on an earlier version you will need to get your system administrator to remove your activity.

Some times it may become necessary to stop and start SQL, this is the relational database behind Dynamics GP. This is usually done to clear down sql temporary tables etc and should never be done with users still in the system (and a viable backup).

However this is the important thing, don’t crash out or reboot or stop and start without seeking advice from your helpdesk/reseller first. It may seem silly, it may be something that you don’t feel you should bother us with, but at the end of the day we would rather be safe than sorry.

Dynamics GP 2010 word templates and required fields to allow emailing or why won’t my modified word template email.

Hi folks this is one we have discovered the hard way. This issue only occurs when you use a modified report as the basis for a word template. In our case it was the remittance advice.


We modified the word template as per the instructions at the end of the Dynamics (GP) 2010 report writer manual. The template was pickup up fine and brought to screen and displayed properly when running the report. The word document could be printed etc. but it would not email.

We talked to Microsoft about this and they advised us that they had had several cases recently where certain fields missing from certain parts of the template had prevented the application identifying the vendor.

Using this as a starting point vendor ID was the obvious place to start looking , however we did have VendorID on the report.

What we discovered was this.

The report writer report needs the VENDORID field from pmRemittancetmp table to be placed anywhere in the report.

Our report used the VENDORID from PM Payment Work in the RH section, we replaced it and all then emailed correctly.

So you need to be careful when modifying reports that you intend to use in a word template, one vendorid is not as good as another. We can also surmise that this is true for many of the other reports.

As and when we find out what the other key fields are on the other reports we will update this blog, in the mean time if you discover any feel free to update the comments.

How to avoid blank address lines in Dynamics GP report writer.

This is one that is cropping up more and more with the advent of electronic mail reading systems. We have had several calls on the helpdesk from clients who have had mailings returned because of blank lines in the address which is stopping the electronic systems used by the post office reading the address correctly.

The issue is caused by the fact that not all customer or creditor addresses are long enough to use all the address fields. This means that there can be blank lines between the last address line used and the post code (Zip code for our cousins across the pond).

You can get round this using calculated fields.

Step1: create a new calculated field in report writer (I am using the RM statement on blank paper).

Step2 : Set the result type to string.

Step3: Select the function tab and then ‘user-defined’ and the function of ‘rw_selectAddrLine’ and insert.

Step 4: Now select the ‘constant’ tab and integer as type and the constant of 1… (this means the first address line. when you do a cal field for address line 2, 3, 4 etc.. the integer value will change accordingly to 1, 2, 3, etc.). Then select add.

Step 5: Next you must select all the address lines and add them.

From this example (RM statement) the resource is ‘rm statement header temp’

Then select address 1, 2, 3 city, state, zip and insert them all one by one

Step 6: Then once all the address lines have been added go to the Constant tab and select the type as ‘string’ and leave the constant blank and click insert

You will notice it inserts ‘’ you must insert 3 of these symbols..

The reason being you need to have 10 conditions..

So we selected the integer as ‘1’ in the beginning and then 6 address lines so we need 3 blank strings to get our total of 10.

Step 7: Now repeat steps 1 to 6 for each of the address lines (the above is the calc field for address line 1) you must create 5 further fields for address line 2,3 4 etc.. and remember the constant integer changes according to the number of the address line you are doing.. 1,2,3 etc..

Step 8: deploy the calculated field address lines in the report one underneath the other. The rw_selectAddrLine’ function will then take care of any blank lines when you run the report selecting the line if populated but if not picking the next populated line.

How to Reconcile RM to GL in Dynamics GP using smartlists.

If you are not on version 10 or 2010 of Dynamics GP then you won’t have the GL reconcile tool. This article is intended to give advice on how to reconcile without this using the functionality in Excel.




If you were in balance last month, then you can create a report for the receivables transactions based on the system date range and then create a report for GL (account transactions) for the system date range. This way, you can compare when the information was physically entered in the system (even with a different document date between modules). If one of the modules has the transaction posted and the other is missing, you can determine if the transactions were posted with a different date in GL than in receivables.



Within Smsrtlists, open up the receivables transaction option. Add the column Posted Date. This column stores the date from your computer of when the document was physically entered in receivables. Even if the document date was for the previous year, the posted date stores the date of when you physically entered it. You can compare the posted date to the posting date (holds the document date) or the document date as well. You can narrow your search by entering this months posted date since you were in balance last month. This will tell you all of the transactions that were entered in receivables since then and you can compare your GL report to that and see if one module is missing the transaction, has a different document date, or a wrong amount as well(possibly the batch was edited in GL before it was posted).



Next, you will want to open the account transaction option within Smartlists. Add the columns Originating Posted Date and Originating Document Number. This stores the posted date (system date) from the originating module. Even if you change the batch date, this stores the date of when it was entered. You can compare the originating posted date to the trx date (document date) in account transactions as well to see if they vary from what your receivables transaction report returns. You may also want to add the Originating Document Number and Originating Master ID (debtor id) to this report, that way you can tick transactions off by using the document number instead of looking for amounts.



These reports will give you a good guideline on trying to narrow down when something was entered, but there could be other possibilities that could be a cause such as a posting interruption (but this may have updated one module and not the other so you can compare what's missing), receivables history was removed (which only effects receivables and not GL).



At this point you can actually use excel functionality to make life easier. If both sheets (RM and GL) are placed in the same excel work book , you can then create an extra column returning a result based on a vlookup between the two sheets , based on originating document number on the GL and document number on the RM , this will do a lot of the hard work for you. You then only need to investigate those transactions not matched up by the Vlookup.

How to free a marked batch without SQL in Dynamics GP

We have just had a call on the helpdesk where the client said “ I have one of those stuck batches, can you stream in and free it in SQL?”.


It occurred to me that we have all become so use to freeing batches marked for posting in Dynamics GP by diving straight into SQL that we have forgotten the lessons we learned back in version 5 when the product was eEnterprise or Dynamics and the manufacturer was Great Plains.

Back then when Dynamics GP was young, the fastest dial up was a 64k modem and we used frustratingly slow software like PC Anywhere to remote access client sites, so we tried to do as much through the front end, over the phone as possible before taking the sql option.

One of these little tricks was master posting. If a receivables batch was showing as marked for posting, but no one had and so could not be accessed, the first stop would be master posting under routines. Here it would usually be showing as available despite showing as marked under receivables batches. To free it up all you needed to do was mark it for posting in master posting and then unmark it. Go back into receivables batches and there it was ready to post.

Well I just tried this on a Dynamics GP version 10 install and guess what, it still works.

So there you have it, how to free up a batch marked for posting without diving into SQL.

Creating new companies in Dynamics GP, what tables should I copy?

Well the simple answer is don’t, most people don’t realize that you no longer need to DTS setup information into a new company from table to table or even import chart of accounts with integration manager.


We get asked this one a lot on the helpdesk as creating a new company is not something most systems administrators do regularly.

There is a copy company setup tool which will do this for you, simply tick what you want it to copy across. Versions 9 and 10 can be downloaded from automated solutions on the following link

https://mbs.microsoft.com/Cms/Templates/document/General.aspx?NRMODE=Published&NRNODEGUID={04AF87A8-8FD9-4BE5-9140-7B34BB2DA5C0}&NRORIGINALURL=/customersource/support/selfsupport/automatedsolutions&NRCACHEHINT=Guest&wa=wsignin1.0


and version 2010 is included in the PSTL tool kit.

It is a lot simpler than using DTS, a word of warning though , be careful when copying company or system specific information you can create redundant entries in tables.