Skip to main content

Notifications

Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / "Ghost" RM Payment cla...
Microsoft Dynamics GP forum
Unanswered

"Ghost" RM Payment claims to be Unposted

(0) ShareShare
ReportReport
Posted on by Crystal Karlson 254
Hi All!
We have a payment (PMT-0645700000000001) stuck in GP somewhere. (This payment was originally posted /accidentally/ as a Weekly Batch from what I have been told.)  It is showing applied to several docs, however, the balances of those docs are not affected, they still show as open for the full amount looking at each doc in Recv Trx Inquiry (by Customer ID). I have run reconciles which appear to fix per the Reconcile report but actually do not. The payment does not show as Work, Open, or History on the account it was created on, maybe because it was not entered but /frequency/ generated?
When we run a HATB including Unposted Applied Credit Documents, all of the docs that this ghost is applied to are fully paid. However, when we run the same HATB, excluding Unposted Applied Credit Documents, the docs show unpaid. 
I cannot find this PMT# in SQL anywhere! I also cannot click on the /Document No./ link in the /Applied from Credits/ window to see this ghost payment, the link does nothing. It's somewhere, but for the life of me I cannot find it.
If anyone can assist, I would GREATLY appreciate it!!!
Thank you in advance!
Categories:
Financial Management
Other
  • Rajiv Sewsarran Profile Picture
    Rajiv Sewsarran 35 on at
    "Ghost" RM Payment claims to be Unposted
    Hi Crystal, 
    declare @DataToFind varchar(100)
set @DataToFind = 'xxx'
--'CBDEP00004537'
--'dep4564'
 
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
 
DECLARE @ISDATE BIT
 
 
 
    INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
    SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
    FROM    Information_Schema.Columns AS C
            INNER Join Information_Schema.Tables AS T
                ON C.Table_Name = T.Table_Name --and C.Table_Name LIKE 'CB%'
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE   Table_Type = 'BASE TABLE'
            And (Data_Type = 'char' OR Data_Type = 'varchar')

 
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
 
SELECT  @SQLTemplate = 'If Exists(Select *
                                From   ReplaceTableName
                                Where  [ReplaceColumnName]
                                             like ''%' + CONVERT(VARCHAR(30), @DataToFind, 126) + '%''
                                )
                          Set @DataExists = 1
                      Else
                          Set @DataExists = 0',
        @PARAMETERS = '@DataExists Bit OUTPUT',
        @i = 1
 
SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp
 
WHILE @i <= @MAX
    BEGIN
        SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
        FROM    @Temp
        WHERE   RowId = @i
 
        PRINT @SQL
        EXEC sp_executesql @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
 
        IF @DataExists =1
            UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
 
        SET @i = @i + 1
    END
 
SELECT  SchemaName,TableName, ColumnName, ' SELECT * FROM ' + TableName + ' WHERE ' + ColumnName + ' = ''' + @DataToFind + '''' AS 'Query String'
FROM    @Temp
WHERE   DataFound = 1
 
GO
     
     
    I've attached the 'find in tables script' The script will scrub through all the tables in SQL to find all occurrences of the record.
     
    Please replace xxx with the payment number  (set @DataToFind = 'xxx') [Line 2 ]
     
     
     
  • Crystal Karlson Profile Picture
    Crystal Karlson 254 on at
    "Ghost" RM Payment claims to be Unposted
    Thank you for your suggestion! I should have included that I ran Check Links and reconciled before posting this. If you have any other suggestions, please share! Thanks again! :)
     
    How about running Microsoft Dynamics GP > Maintenance > Check Links for Series: SAles Logical Table Receivables Open Transaction Files?
  • Lisa at AonC.com Profile Picture
    Lisa at AonC.com 2,965 on at
    "Ghost" RM Payment claims to be Unposted
    How about running Microsoft Dynamics GP > Maintenance > Check Links for Series: SAles Logical Table Receivables Open Transaction Files?

Helpful resources

Quick Links

Take the Community feedback survey!

Answer this brief 15-question survey about your Community experience…

Demystifying Copilot: Service Edition with Sundar Raghavan

Sundar answers more questions about Copilot for Service...

Dynamics 365 Business Central vs Finance and SCM

Take a look at the key differences between Business Central and…

more Community News

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,377 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 223,308 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,140

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans