Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 41 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,998 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

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Community Spotlight for September 2024

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,326 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,166 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans