RM30201 apply to amounts on payment is not right

(0) Share

Report

Posted on by Community Member Microsoft Employee

We have had this come up a couple of times over the past months. There will be a difference between the Historical Aged Trial Balance and the Age Trial balance, which alerts us to the issue. Here is the issue:

A payment is fully applied and in history. When I click on the 'Amount Remaining' to see what invoices are applied to the payment, I realize that the apply amount do not add up. When I look at one of the invoices, it was for the amount that was applied on the payment plus the missing amount. So, everything adds up, just not on the Apply to records.

When we look up the original records of when the payment was applied, everything checks out. This issue was dormant for a few weeks and then it just popped up.

I will fix it by modifying the apply to amounts in the RM30201 table, but my question is- has this happened to anyone else?? How could this have happened?

*This post is locked for comments

All responses (10)

Answers (0)

StefanieC 432 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

I was looking at this for what I think is a similar issue. I believe this would be the RM script.

/*

Every now and then you get posting interruptions in Dynamics GP at exactly the wrong time. Normally

the Batch recovery routine under tools>routines>Batch Recovery, picks these up. However sometimes

this is not the case and you then need to determine what state your data is in within the Dynamics GP product.

The below script is designed to help in these circumstances. It is run against the Dynamics GP

company database and is designed to find those payables transactions with missing apply details.

I would recommend running this script if you have found a PM transaction with missing apply details

to ascertain the extent of the problem.

If you do have a posting interruption or if you find that you do have missing apply info please contact the

helpdesk@touchstone.co.uk and we can investigate further. If you have any questions about the script

etc please feel free to contact me andrew.hall@touchstone.co.uk

Here is the script:

*/

/* Before running this query go to query on the menu bar and select results in text

This query is designed to find open transactions that have missing apply info Open & the info is

in history

4 temp tables are created in the temp db

*/

/*The first part of the script selects transactions from

the RM20101 which have apply info in the RM20201 */

SELECT b.DOCNUMBR

, b.CUSTNMBR

INTO #temp1

FROM RM20201 a

, RM20101 b

WHERE b.docnumbr = a.aptodcnm

AND a.CUSTNMBR = b.CUSTNMBR

go

/* The second part of the script selects transactions from

the RM20101 which should have apply info but they did not appear in the

results of the first script */

SELECT DOCNUMBR

, CUSTNMBR

INTO #temp2

FROM RM20101

WHERE ORTRXAMT > CURTRXAM

AND curtrxam > '0.00000'

AND DOCNUMBR NOT IN (SELECT DOCNUMBR

FROM #temp1)

ORDER BY CUSTNMBR

go

/* The third part of the script selects transactions from

the results of the second script which have apply info in the

PM30300 History table */

SELECT a.DOCNUMBR

, a.CUSTNMBR

INTO #temp3

FROM #temp2 a

, RM30201 b

WHERE a.CUSTNMBR = b.CUSTNMBR

AND a.DOCNUMBR = b.APTODCNM

go

/* The Fourth part of the script selects transactions from

the RM20101 that do not appear in the results of the third part or the first part

( open apply info and History apply info) */

SELECT DOCNUMBR

, CUSTNMBR

INTO #temp4

FROM RM20101

WHERE ORTRXAMT > curtrxam

AND curtrxam > '0.00000'

AND DOCNUMBR NOT IN (SELECT DOCNUMBR

FROM #temp3)

AND DOCNUMBR NOT IN (SELECT DOCNUMBR

FROM #temp1)

ORDER BY CUSTNMBR

go

/* This part of the script displays the results*/

PRINT 'Transactions from RM20101 missing apply info in RM20201 & RM30201'

SELECT *

FROM #temp4

go

Use tempdb

GO

/* Use this script to drop the temp tables if they persist after

the session closes. You have to close the query window

to end the session */

Select 'DROP TABLE ' + name from sys.objects where name like '%#temp%' and type = 'U'

Select * from sys.objects where name like '%#temp%'
L Vail 65,271 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

Good point. It's the same concept, but hopefully after a year they've got it figured out. You are correct, I missed the fact that RM was the question, not payables.

Leslie
Elizabeth Hodge 162 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

This is the payables script. I believe this was a receivables apply question.
Suggested answer

L Vail 65,271 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

Hi Meredith,

I have a sql statement that I use to check the validity of the apply records against the originating documents and identify any discrepancies. I got the query from David Musgrave or Victoria Yudin, I can't recall which. Here' the SQL statement:

/*

Every now and then you get posting interruptions in Dynamics GP at exactly the wrong time. Normally

the Batch recovery routine under tools>routines>Batch Recovery, picks these up. However sometimes

this is not the case and you then need to determine what state your data is in within the Dynamics GP product.

The below script is designed to help in these circumstances. It is run against the Dynamics GP

company database and is designed to find those payables transactions with missing apply details.

I would recommend running this script if you have found a PM transaction with missing apply details

to ascertain the extent of the problem.

If you do have a posting interruption or if you find that you do have missing apply info please contact the

helpdesk@touchstone.co.uk and we can investigate further. If you have any questions about the script

etc please feel free to contact me andrew.hall@touchstone.co.uk

Here is the script:

*/

/* Before running this query go to query on the menu bar and select results in text

This query is designed to find open transactions that have missing apply info Open & the info is

in history

4 temp tables are created in the temp db

*/

/*The first part of the script selects transactions from

the PM20000 which have apply info in the PM10200 */

SELECT b.vchrnmbr

, b.vendorid

INTO #temp1

FROM pm10200 a

, pm20000 b

WHERE b.docnumbr = a.aptodcnm

AND a.vendorid = b.vendorid

go

/* The second part of the script selects transactions from

the PM20000 which should have apply info but they did not appear in the

results of the first script */

SELECT vchrnmbr

, vendorid

INTO #temp2

FROM pm20000

WHERE docamnt > curtrxam

AND curtrxam > '0.00000'

AND vchrnmbr NOT IN (SELECT vchrnmbr

FROM #temp1)

ORDER BY vendorid

go

/* The third part of the script selects transactions from

the results of the second script which have apply info in the

PM30300 History table */

SELECT a.vchrnmbr

, a.vendorid

INTO #temp3

FROM #temp2 a

, pm30300 b

WHERE a.vendorid = b.vendorid

AND a.vchrnmbr = b.vchrnmbr

go

/* The Fourth part of the script selects transactions from

the PM20000 that do not appear in the results of the third part or the first part

( open apply info and History apply info) */

SELECT vchrnmbr

, vendorid

, docnumbr

INTO #temp4

FROM pm20000

WHERE docamnt > curtrxam

AND curtrxam > '0.00000'

AND vchrnmbr NOT IN (SELECT vchrnmbr

FROM #temp3)

AND vchrnmbr NOT IN (SELECT vchrnmbr

FROM #temp1)

ORDER BY vendorid

go

/* This part of the script displays the results*/

PRINT 'Transactions from PM20000 missing apply info in PM10200 & PM30300'

SELECT *

FROM #temp4

go

Use tempdb

GO

/* Use this script to drop the temp tables if they persist after

the session closes. You have to close the query window

to end the session */

Select 'DROP TABLE ' + name from sys.objects where name like '%#temp%' and type = 'U'

Select * from sys.objects where name like '%#temp%'

I hope this can help you resolve you error

Kind regards,

Leslie
Community Member Microsoft Employee on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

We have run across a similar situation with RM records showing fully applied, without showing any payment record at all in the application when drilling into the GP windows. I've ran the Reconcile for this customer and Check Links as suggested to no avail. Did you ever find a resolution for this topic?
Suggested answer

Tom Cruse 1,475 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

It sounds like to me you have a "ghost payment", not a real term but somewhere along the way during posting there was probably an interruption and a payment was never fully posted but was applied to the invoice when in actuality it doesn't exist which is why the invoice is in history as fully applied but with 959.91 "missing".

My thoughts would be my original suggestion. I've cleared up similar mistakes like this before.

Run reconcile and possibly limit the range to that specific customer and if that doesn't correct it then run CheckLinks on Receivables, probably the Receivables Transaction History Files.

As always don't forget to make a backup first in case the results returned are not what you want or expect since they are irreversible.
Community Member Microsoft Employee on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

A screenshot would better explain this, but I'll try to be more clear. I've looked at it through GP and through SQL.

Here is what it looks like in GP:

1. I pull up the payment (amount is $2735.65) in Transaction Inquiry:

-It shows as fully applied and in history

-there are 3 invoices applied to the payment

-If I add up the apply amount of the three invoices the total is 959.91 less than the payment.

- the first invoice apply amount is 522.84

2. I look up the first invoice applied to the payment

- It shows in history with no amount remaining

- the amount of the invoice 1482.75

- It is ONLY applied from one payment (the one above), but the apply amount is 522.84.

- The difference is 959.91

For some reason in the apply record, the 959.91 is gone.

This happened over the summer as well. We tried using the RM unapply tool to fix it (the reapplying the documents), but the apply amount was still missing. We ended up updating the RM30201 record with the correct amounts. Which is what we'll probably do again.

I just cannot figure out why this is happening. That's really my question.... Any ideas??
Suggested answer

Tom Cruse 1,475 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

You said the apply amount is "not the full amount of the payment or invoice", is that because multiple payments are applied to that invoice or multiple invoices are paid with a payment. Are you doing this all in SQL or through GP application. You said you're in RM30201, what happens if you look in GP, what does it show there?

If this isn't the case then I would suggest the next step.

I would run reconcile and possibly limit the range to that specific customer and if that doesn't correct it then run CheckLinks on Receivables Transaction History Files.

As always don't forget to make a backup first in case the results returned are not what you want or expect since they are irreversible.
Community Member Microsoft Employee on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

Both the payment and the invoice are in history. They both show as fully applied (amount remaining is zero). But when I drill down on the amount remaining, the apply amount (from the rm30201 table) is not the full amount of the payment or the invoice. They payment and invoice fit together completely, but somehow a chunk of it is separated on the apply table.

There is nothing in the open table for these transactions.

Does this make sense??
Suggested answer

Tom Cruse 1,475 on at

Like (0)

Report

RE: RM30201 apply to amounts on payment is not right

Are you saying that you have a payment that's applied to a historical invoice and a current invoice because the current invoice has not been fully paid???

I'm a little confused on your question. Are you looking in RM20201 too?