Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / "Ghost" RM Payment cla...
Finance | Project Operations, Human Resources, ...
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:
Microsoft Dynamics GP
  • 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?
  • Suggested answer
    Lisa at AonC.com Profile Picture
    Lisa at AonC.com 576 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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,904 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,605 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans