Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Unanswered

"Ghost" RM Payment claims to be Unposted

Posted on by 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!
  • 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…

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