15
We are running GP 2018.
When attempting to apply a payment to an invoice in the Apply Sales Documents window, one of our team members received the following message when they selected the payment they wanted to apply
You can't choose this document because it's part of a recovered batch or is still in the process of posting in Apply Sales Documents window in Dynamics GP
There is no batch in the batch recovery window. Do you have any ideas how to resolve this issue?
Looks like it's blank for me too. I uploaded as a file in my profile. I can see the file there, but can't open it. I did try just now uploading the script and it worked this time so give that a shot.
DECLARE @phrase VARCHAR(8000) = 'DEBIT000000019047'
--WHAT TO SEARCH FOR - SQL KEYWORDS/CHARS ACCEPTED LIKE '%TEST%'
DECLARE @TableExclusionListString VARCHAR(MAX)=NULL
--FOR EXAMPLE, REPLACE NULL WITH 'RM30201,RM30101' TO SKIP RM30201 AND RM30101
--CAN USE TABLE SIZE SCRIPT TO SKIP LARGE, UNNECESSARY TABLES
DECLARE @TableInclusionMask VARCHAR(MAX)=NULL
--FOR EXAMPLE, REPLACE NULL WITH 'RM%' TO SEARCH ONLY TABLES THAT START WITH RM
--CAN USE ALONG WITH EXCLUSION TO SEARCH ALL RM% TABLES BUT SKIP RM30201 AND RM30101
DECLARE @sql VARCHAR(8000)
DECLARE @schema VARCHAR(128)
DECLARE @holdschema VARCHAR(128)
DECLARE @tbl VARCHAR(128)
DECLARE @holdtbl VARCHAR(128)
DECLARE @currentobjmsg VARCHAR(128)
DECLARE @col VARCHAR(128)
DECLARE @id_present BIT
DECLARE @is_char_phrase BIT
DECLARE @min_len INT
DECLARE @loop_idx INT
DECLARE @loop_chr CHAR(1)
DECLARE @TableExclusionListTable TABLE (TblName VARCHAR(MAX))
DECLARE @xml XML
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#tbl_res') IS NOT NULL
DROP TABLE #tbl_res
IF @TableExclusionListString IS NOT NULL
BEGIN
SELECT @xml = CAST('<A>' + REPLACE(@TableExclusionListString, ',', '</A><A>') + '</A>' AS XML)
INSERT INTO @TableExclusionListTable
SELECT t.value('.', 'VARCHAR(MAX)')
FROM @xml.nodes('/A') AS X(T)
END
IF ISNULL(@phrase, '') = ''
BEGIN
RAISERROR ('Phrase is absent',16,-1)
RETURN
END
-- Handle Quotes passed in the search string
SET @phrase = REPLACE(@phrase, '''', '''''')
SELECT
@loop_idx = 1,
@is_char_phrase = 0,
@min_len = 0
WHILE @loop_idx <= LEN(@phrase)
BEGIN
SET @loop_chr = SUBSTRING(@phrase, @loop_idx, 1)
IF @loop_chr NOT IN ('%', '_')
SET @min_len = @min_len + 1
IF @is_char_phrase = 0
AND @loop_chr NOT IN ('%', '_', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')
SET @is_char_phrase = 1
SET @loop_idx = @loop_idx + 1
END
CREATE TABLE #tbl_res (
SchemaName VARCHAR(128) NOT NULL,
TableName VARCHAR(128) NOT NULL,
ColumnName VARCHAR(128) NOT NULL,
Id INT NULL,
ColumnValue VARCHAR(7500) NOT NULL
)
DECLARE CRR CURSOR LOCAL FAST_FORWARD
FOR
SELECT s.name,t.name,c.name,1
FROM
sysobjects t
INNER JOIN syscolumns c ON c.id = t.id
INNER JOIN sys.schemas s ON t.uid = s.schema_id
WHERE
t.type = 'U'
AND
c.STATUS & 0x80 = 0 -- Not IDENTITY
AND
EXISTS (SELECT * FROM syscolumns c2 WHERE t.id = c2.id AND c2.STATUS & 0x80 = 0x80 AND c2.xtype IN (48, 52, 56))
AND
(
(
@is_char_phrase = 1
AND c.xtype IN (35, 99, 167, 175, 231, 239, 35)
AND c.length >= @min_len
) --char only fields: text,ntext,varchar,char,nvarchar,nchar
OR
(
@is_char_phrase = 1
AND c.xtype IN (36)
) --handles GUIDs
OR
(
@is_char_phrase = 0
AND c.xtype NOT IN (34, 58, 61, 165, 173, 189, 241)
) --disregard these types: image,smalldatetime,datetime,varbinary,binary,timestamp,xml
OR
(
ISDATE(@phrase)=1
AND C.xtype IN (58,61)
) --include dates if phrase like date
)
AND
t.name NOT IN (SELECT TblName FROM @TableExclusionListTable)
AND
T.name LIKE CASE WHEN @TableInclusionMask IS NULL THEN '%' ELSE @TableInclusionMask END
--AND LEN(T.name) IN (7, 8)
--AND T.name NOT LIKE 'ZZ%' AND T.name NOT LIKE '%''%'
UNION
SELECT s.name,t.name,c.name,0
FROM
sysobjects t
INNER JOIN syscolumns c ON c.id = t.id
INNER JOIN sys.schemas s ON t.uid = s.schema_id
WHERE
t.type = 'U'
AND
NOT EXISTS (SELECT * FROM syscolumns c2 WHERE t.id = c2.id AND c2.STATUS & 0x80 = 0x80 AND c2.xtype IN (48, 52, 56))
AND
(
(
@is_char_phrase = 1
AND c.xtype IN (35, 99, 167, 175, 231, 239, 35)
AND c.length >= @min_len
) --char only fields: text,ntext,varchar,char,nvarchar,nchar
OR
(
@is_char_phrase = 1
AND c.xtype IN (36)
) --handles GUIDs
OR
(
@is_char_phrase = 0
AND c.xtype NOT IN (34, 58, 61, 165, 173, 189, 241)
) --disregard these types: image,smalldatetime,datetime,varbinary,binary,timestamp,xml
OR
(
ISDATE(@phrase)=1
AND C.xtype IN (58,61)
) --include dates if phrase like date
)
AND
t.name NOT IN (SELECT TblName FROM @TableExclusionListTable)
AND
T.name LIKE CASE WHEN @TableInclusionMask IS NULL THEN '%' ELSE @TableInclusionMask END
--AND LEN(T.name) IN (7, 8)
--AND T.name NOT LIKE 'ZZ%' AND T.name NOT LIKE '%''%'
ORDER BY 1,
2
OPEN CRR
FETCH CRR INTO @schema,@tbl,@col,@id_present
SELECT
@holdschema = @schema,
@holdtbl = @tbl,
@currentobjmsg = 'Current object: ' + @schema + '.' + @tbl
RAISERROR (@currentobjmsg,0,1) WITH NOWAIT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'insert into #tbl_res (SchemaName,TableName, ColumnName, Id, ColumnValue) ' + 'select ''[' + REPLACE(@schema,'''','''''') + ']'',''[' + REPLACE(@tbl,'''','''''') + ']'', ''[' + REPLACE(@col,'''','''''') + ']'', '
IF @id_present = 1
SET @sql = @sql + 'IDENTITYCOL, '
ELSE
SET @sql = @sql + 'NULL, '
SET @sql = @sql + 'CONVERT(varchar(7500), [' + @col + ']) ' + 'from [' + @schema + '].[' + @tbl + '] (nolock) ' + 'where CONVERT(varchar(8000), [' + @col + '],101) like ''' + @phrase + ''' '
IF @holdschema <> @schema
OR @holdtbl <> @tbl
BEGIN
SELECT @currentobjmsg = 'Current object: ' + @schema + '.' + @tbl
RAISERROR (@currentobjmsg,0,1) WITH NOWAIT
SELECT
@holdschema = @schema,
@holdtbl = @tbl
END
EXEC (@sql)
FETCH CRR INTO @schema,@tbl,@col,@id_present
END
CLOSE CRR
DEALLOCATE CRR
SELECT
SchemaName [Schema],
TableName [Table],
ColumnName [Column],
CONVERT(VARCHAR(255), ColumnValue) ColumnValue,
'SELECT ''' + TableName + ''',* FROM ' + SchemaName + '.' + TableName + ' WHERE ' + ColumnName + '=''' + CONVERT(VARCHAR(255), ColumnValue) + '''' SQLScript
FROM #tbl_res
GROUP BY SchemaName,TableName,ColumnName,ColumnValue
ORDER BY TableName
PRINT '***Completed***'
This link should work: community.dynamics.com/.../searchalltablesforstring.txt
If those transactions are in the AR transaction work table (RM10301) and AR transaction open table (RM20101) then that should not be the case and they would need removed from one of them. If the transactions properly hit the GL (GL20000), then should be safe to assume they need removed from work RM10301. Would also need to then update RM00401.DCSTATUS from 1 to 2 if going that route.
I have a script that you can run that scans all tables/columns for a specific value and returns list of everything it finds. With those results you could determine what records are in what tables that they should not be in. Unfortunately, when I try to put the script in here, I run into an error from the forum. If you're interested I can find an alternate way of providing this to you.
The payment was in a financial batch that has since been posted. The user can apply the payment to the desired transaction.
The Debit Memos are in the Work and Open tables
DEBIT000000019047
DEBIT000000019048
DEBIT000000019049
DEBIT000000019050
DEBIT000000019051
Any time the user tries to apply a payment to any of these documents, the message appears saying the transactions are part of a recovered batch or a batch that is in the process of being posted.
It's difficult to say offhand, but I wonder if the invoice and debit memo transaction(s) are stuck in the unposted tables (RM10301).
Could try this:
SELECT * FROM RM10301 WHERE DOCNUMBR='<document number you're having issues with>'
Or possibly there's an unposted credit or payment that is applied to one of these transactions. Could check that with this:
SELECT * FROM RM20201 WHERE APTODCNM='<document number you're having issues with>'
Let me know whether that turns anything up.
Lance Brigham
Principal Consultant
Velosio
In addition to the payment reported yesterday, the same team member reported a Debit Memo returned the same result. In this scenario, the Customer and Payment could be selected. When the user clicked the document they wanted to apply the payment to, they received the same error as the payment produced yesterday:
You can't choose this document because it's part of a recovered batch or is still in the process of posting in Apply Sales Documents window in Dynamics GP
After discussing this, the user mentioned they entered some Debit Memos and Posted them as transactions (They did not put them in a batch then post the batch). Of the transactions that were posted, one is in a financial batch RMSLS000000#####) that has not been posted.
I asked the user to post this financial (RMSLS) batch. After which, the ability to apply a payment to any of these debit memos, or the ability to apply the payment (reported yesterday) to an invoice can be verified.
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,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (