GP to BC Fundamentals Video- General Posting Groups

As I’ve mentioned several times on my blog I originally come from a Dynamics GP background so making the transition to Dynamics 365 Business Central has been a real learning journey for me, and one that is still ongoing.

Along the way I’ve managed to learn some important concepts which I hope to share in a series of short videos.

The first one focuses on how GP and BC differ when setting up and selecting default GL codes.

Hopefully new GP to BC users will find it useful. (or anyone new to BC for that matter)

Thanks for reading!

Dynamics GP Vs Dynamics 365 Business Central – Multi currency revaluation / Adjust Exchange Rates

Introduction

This is another blog in a series I’ve been writing comparing functionality in Dynamics GP to Dynamics 365 Business Central.

In this post I’m looking at multi currency revaluation in Dynamics GP compared with adjust exchange rates in Dynamics 365 Business Central.

Both routines perform the same task of revaluing foreign currency entries, and both are great and easy to use solutions, however I’ve found some key differences which I’ll highlight below.

** Please note this article doesn’t go into how rates are selected and what filters can be used. It focuses on the options available to revalue.

Dynamics GP

In Dynamics GP you run the revaluation using the “Multi currency Revaluation” window below:

The first thing to note is the revaluation routine is ran separately for the Financial (GL), Sales and Purchasing modules and for all three modules you can select to run a “Realised” or “Unrealised” revaluation.

In my experience a “Realised” revaluation is only ever ran on the financial series against G/L codes for foreign currency bank accounts. However the option exists to run a realised revaluation against the sales and purchase series as well.

** I suspect this would be useful if a sales or purchase transaction had either been outstanding on the ledger for a while, or was expected to be outstanding on the ledger for a while, and in that time a significant change in the exchange rate had, or would occur.

When you do run a “Realised” revaluation on the sales or purchasing series the functional (LCY) currency amounts are updated on the original transaction, and G/L entries are posted to the relevant realised exchange gains and losses accounts.

The “Unrealised” revaluation can also be ran against the Financial, Sales and Purchasing series however in my experience this is only generally ran against the Sales and Purchasing series.

Interestingly the “unrealised” revaluation can be ran with or without the “reversing” option being ticked.

With the option ticked entries are posted to the unrealised gains and losses accounts as expected however a reversing journal is also posted on the date specified backing out those postings. Also, when this is selected, the functional amounts (LCY amounts) on the original sales or purchasing transactions aren’t updated.

When the option for a reversing entry isn’t ticked the unrealised gains and losses are updated along with the functional amounts on the original transactions. When the transaction is applied the unrealised gains and losses are reversed and the realised gains and losses are updated. (in my experience this is the option that is most commonly used)

Finally you can also “print report only” prior to running the actual revaluation. This gives you a sneak preview of what would happen if you were to run the revaluation. I find this very useful and always recommend using this prior to posting the revaluation.

Dynamics 365 Business Central

The window below is used to run the “Adjust Exchange Rates” in Dynamics 365 Business Central:

The first thing to note is that in Dynamics 365 Business Central there’s no option to run the “Adjust Exchange Rates” separately for each ledger (or series).

There’s also no option to revalue the G/L codes other than the bank accounts. (which are revalued by adjusting the bank account ledger entries. The G/L entries aren’t affected)

** This is because interestingly unlike Dynamics GP the foreign currency amounts aren’t stored in the G/L Entries table. Only “additional currency amounts” are stored in the G/L entries….more on that later.

In also interesting to note that unlike Dynamics GP there’s no specific option to run a “realised” or “unrealised” revaluation.

When you run the “Adjust Exchange Rates” job and select “Adjust Customer, Vendor and Bank Accounts” the system posts adjustments to the “unrealised” gains and losses for the Customer and Vendor Ledger entries and to “realised” gains and losses accounts for bank accounts. Any “unrealised” gains and losses are tracked on the original transactions via “Detailed Ledger Entries”. As with Dynamics GP these are reversed when the transaction is applied and amounts posted to the “realised” exchange gains\losses accounts.

Dynamics 365 Business Central also offers the ability to record transactions in an “Additional Reporting Currency”. Although this is beyond the scope of this article, when this is configured amounts are posted to the G/L entries in the selected reporting currency using the current rate for the selected additional currency. Depending on the configuration of the G/L code these can be adjusted by selecting the option “Adjust G/L accounts for Add. Reporting Currency” in the “Adjust Exchange Rate” window as per above.

Although you can specify a reporting currency in Dynamics GP you can’t revalue it in the same way you can in Dynamics 365 Business Central.

Conclusion

I’ve found there are quite a number of differences in functionality between Dynamics GP and Dynamics 365 Business Central in this particular area.

For example you can’t run a realised revaluation of the Sales and Purchasing ledgers or revalue G/L codes other than bank accounts. There’s also no option to preview the potential posting prior to running the routine.

This said, in my experience, the functionality offered by Dynamics 365 Business Central would be adequate for all Dynamics GP users thinking of making the transition to Dynamics 365 Business Central.

** Please note based on my findings running the “Adjust Exchange Rates” job and selecting “Adjust Customer, Vendor and Bank Accounts” is the equivalent of running a realised revaluation on the financial series restricted to bank accounts, and running the unrealised revaluation in Dynamics GP on the Sales and Purchasing series and not ticking the “reversing” option.

I’d be interested to hear if anyone knows of users needing to run realised revaluations of the Sales and Purchasing ledgers and the reasons for this. Also if anyone runs an unrealised revaluation and marks the option to reverse this.

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!