I originally applied the amount to check 5286, but realized, I should have used 5277.2 and went back in, unchecked 5286 and tried to select 5277.2. That did not work, so I tried to put it back on to 5286 and I got the message below:
"This credit document is fully applied."
Than I went into the billing section for this customer and that check did not apply to SRV049272. We are not sure how to make it apply now…
*This post is locked for comments
this is the solution, worked with me for the same issue.
Suggested Answer
What is the current transaction amount for this document ?
Have you considered the reconcile sales document (Microsoft Dynamics GP > Tools > Utilities > Sales > Reconcile), for the outstanding document amounts specifically ?
Your feedback is highly appreciated,
Hi,
Do you have access to SQL? In addition to checking the inquiry windows and Mahmoud's suggestion, can you look at the tables in SQL? The transactions themselves (both the invoice and the payment) should be in the PM20000 or the PM30200, one is the Open table and the other is the History table. You should be able to see the CURTRXAM in those tables. The matching apply table should have a record for any document that has reduced the CURTRXAM. The apply tables are PM10200 and PM30300. The apply table tells you which documents applied to what. There is a SQL statement that you can run that will look at each of these tables and report on missing documents. I cannot remember where I got it, but rest assured, I didn't write it. It's long, but I don't have a link for you, so I'm just going to copy it below.
Kind regards,
Leslie
/* SQL PM Find Missing Apply Records
This statement goes through the pm30200 table and looks for any record that does
not have a matching apply document(s) to satisfy the balance*/
SET quoted_identifier OFF
SET nocount ON
PRINT "Deleting Temp tables (if they exist)"
go
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id('tempdb..#PMTrans')
AND type = 'U')
DROP TABLE #pmtrans
go
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id('tempdb..#PMApply')
AND type = 'U')
DROP TABLE #pmapply
/* Create the table used to hold PM30200 Info */
PRINT "Creating Temp tables"
go
CREATE TABLE #pmtrans
(
vchrnmbr# CHAR (21) NULL
, docnumbr# CHAR (21) NULL
, doctype# SMALLINT NULL
, billmode# TINYINT NULL
, vendor CHAR (15) NULL
, DocAmtAfterWriteOff NUMERIC(19, 5) NULL
, docdate DATETIME NULL
)
CREATE INDEX #pmtrans_pk
ON #pmtrans (vchrnmbr#, doctype#, billmode#, vendor,
DocAmtAfterWriteOff)
/* Create the table used to hold Sum of Apply Records from PM30300 */
go
CREATE TABLE #pmapply
(
vchrnmbr# CHAR (21) NULL
, doctype# SMALLINT NULL
, billmode# TINYINT NULL
, vendor CHAR (15) NULL
, totalappliedamt NUMERIC(19, 5) NULL
, writeoffamt NUMERIC(19, 5) NULL
, discountamt NUMERIC(19, 5) NULL
, realizedamt NUMERIC(19, 5) NULL
, origcurrency INT NULL
)
CREATE INDEX #pmapply_pk
ON #pmapply (vchrnmbr#, doctype#, billmode#, vendor, totalappliedamt,
writeoffamt, discountamt, realizedamt, origcurrency)
PRINT "Selecting Bills in History"
go
INSERT #pmtrans
(vchrnmbr#
, docnumbr#
, doctype#
, billmode#
, vendor
, DocAmtAfterWriteOff
, docdate)
SELECT vchrnmbr
, docnumbr
, doctype
, 0
, vendorid
, ( docamnt - distknam )
, docdate
FROM pm30200
WHERE doctype < 4
AND voided = 0
AND ( docamnt - distknam ) <> 0.0 -- Zero Value Documents
ORDER BY vchrnmbr
INSERT #pmtrans
(vchrnmbr#
, docnumbr#
, doctype#
, billmode#
, vendor
, DocAmtAfterWriteOff
, docdate)
SELECT vchrnmbr
, docnumbr
, doctype
, 1
, vendorid
, ( docamnt - distknam )
, docdate
FROM pm30200
WHERE doctype >= 4
AND voided = 0
AND ( docamnt - distknam ) <> 0.0 -- Zero Value Documents
AND NOT ( doctype = 4
AND ttlpymts = docamnt ) -- Remove fully paid returns
ORDER BY vchrnmbr
PRINT "Selecting Apply Records and Totally Apply Credits in History"
go
/* Get Functional Currency ID */
DECLARE @FUNC VARCHAR(5)
SELECT TOP 1 @FUNC = funlcurr
FROM mc40000
INSERT #pmapply
(vchrnmbr#
, doctype#
, billmode#
, vendor
, totalappliedamt
, writeoffamt
, discountamt
, realizedamt
, origcurrency)
SELECT aptvchnm
, aptodcty
, 0
, vendorid
, Sum(appldamt)
, Sum(wrofamnt)
, 0
, 0
, Sum (CASE
WHEN @FUNC != ''
AND @FUNC != curncyid THEN 1
ELSE 0
END)
FROM pm30300
GROUP BY aptvchnm
, aptodcty
, vendorid
ORDER BY aptvchnm
INSERT #pmapply
(vchrnmbr#
, doctype#
, billmode#
, vendor
, totalappliedamt
, writeoffamt
, discountamt
, realizedamt
, origcurrency)
SELECT vchrnmbr
, doctype
, 1
, vendorid
, Sum(appldamt)
, 0
, Sum(distknam)
, Sum(rlganlos)
, Sum (CASE
WHEN @FUNC != ''
AND @FUNC != curncyid THEN 1
ELSE 0
END)
FROM pm30300
GROUP BY vchrnmbr
, doctype
, vendorid
ORDER BY vchrnmbr
PRINT "Entries that are missing apply records"
go
SELECT a.billmode#
, a.vchrnmbr#
, a.docnumbr#
, a.doctype#
, a.vendor
, a.docdate
, a.DocAmtAfterWriteOff
, Isnull(b.totalappliedamt, 0)
AS TotalAppliedAmt
, Isnull(( a.DocAmtAfterWriteOff - (
b.totalappliedamt + b.writeoffamt -
b.discountamt
- b.realizedamt ) ), 0) AS
Differ
FROM #pmtrans a
LEFT OUTER JOIN #pmapply b
ON a.vchrnmbr# = b.vchrnmbr#
AND a.doctype# = b.doctype#
AND a.billmode# = b.billmode#
AND a.vendor = b.vendor
WHERE ( ( Isnull(( a.DocAmtAfterWriteOff - (
b.totalappliedamt + b.writeoffamt -
b.discountamt
-
b.realizedamt ) ), 0) <> 0 )
OR ( b.vendor IS NULL ) )
AND ( a.billmode# = 0
OR Isnull(b.origcurrency, 0) = 0
OR ( Abs(Isnull(( a.DocAmtAfterWriteOff - (
b.totalappliedamt + b.writeoffamt
-
b.discountamt
-
b.realizedamt ) ), 0)) > (
Isnull(b.origcurrency, 0) / 100.0 )
) ) -- Rounding on Credit Documents
ORDER BY a.vendor
PRINT "Removing Temp tables"
go
DROP TABLE #pmtrans
DROP TABLE #pmapply
What is the current transaction amount for this document ?
Have you considered the reconcile sales document (Microsoft Dynamics GP > Tools > Utilities > Sales > Reconcile), for the outstanding document amounts specifically ?
Your feedback is highly appreciated,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (