web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / AP Invoice: Move from ...
Microsoft Dynamics GP (Archived)

AP Invoice: Move from History to Open?

(0) ShareShare
ReportReport
Posted on by Community Member

We had a system interruption while applying a payment in AP.  This interruption caused the invoices to show as OPEN yet we could not unapply or void them because the payment was missing.  The PM20100 table was then deleted which then caused the invoices to move to HISTORY yet still there is no payment associated with these invoices.  I thought to void the the historical invoices but they are not showing up in the Void Historical Transactions window.  Now we have these invoices that have NOT BEEN PAID showing in history.  Is there a way for us to get these invoices back into an OPEN status?

*This post is locked for comments

I have the same question (0)
  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Fonda,

    Please take a backup of database and sql and try the following.

    SELECT * FROM DYNAMICS..ACTIVITY

    SELECT * FROM DYNAMICS..SY00800

    SELECT * FROM DYNAMICS..SY00801

    SELECT * FROM TEMPDB..DEX_LOCK

    SELECT * FROM TEMPDB..DEX_SESSION

    If you get answers / results to any of the above, make sure everyone is in fact logged out of GP and then run the following scripts to clear the tables.

    DELETE DYNAMICS..ACTIVITY

    DELETE DYNAMICS..SY00800

    DELETE DYNAMICS..SY00801

    DELETE TEMPDB..DEX_LOCK

    DELETE TEMPDB..DEX_SESSION

    If you still didn't get resolved please run checklink against AP.

  • Community Member Profile Picture
    Community Member on at

    Hi Babu.  I ran this process before and it didn't change anything.  So I just ran it again along with check links and still the invoices are showing as HIST.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,852 Moderator on at

    You will need to remove these transactions and then enter them again along with the payments and apply them all over again. Make sure you turn off posting to the GL as these transactions have already been posted. Tools->Utilities->Purchasing->Remove Transaction History will allow you to remove the invoices. I would do this work after hours when no one else is in GP and after performing a backup. Make sure you insert the document range before pressing the remove button or you will lose all your purchasing history.

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    I have had a similar posting interruption case which resulted with orphan records in the history tables, I have created this script to identify the corrupted invoices as a first step:

    SELECT  A.DOCNUMBR ,
        A.DOCTYPE ,
        A.DOCAMNT ,
        A.CURTRXAM ,
        ISNULL(B.AppliedAmount, 0) AppliedAmount ,
        A.DOCAMNT - ISNULL(B.AppliedAmount, 0) CONTROL_Amount
FROM    ( SELECT    *
          FROM      PM30200 AS A -- WHERE A.DOCNUMBR = '295204' 
          
        ) AS A
        LEFT OUTER JOIN ( SELECT    VendorID ,
                                    APTODCNM ,
                                    APTODCTY ,
                                    SUM(AppliedAmount) AppliedAmount
                          FROM      ( SELECT    VendorID ,
                                                APTODCNM ,
                                                APTODCTY ,
                                                CASE CURNCYID
                                                  WHEN 'SR'
                                                  THEN ActualApplyToAmount
                                                  ELSE APPLDAMT
                                                END AS AppliedAmount
                                      FROM      PM30300 -- WHERE APTODCNM = '90002667'
                                      
                                    ) AS X
                          GROUP BY  APTODCNM ,
                                    APTODCTY ,
                                    VENDORID --HAVING  APTODCNM = '295204'
                          
                        ) AS B ON A.DOCNUMBR = B.APTODCNM
                                  AND A.DOCTYPE = B.APTODCTY
                                  AND A.VENDORID = B.VENDORID
WHERE   A.DOCTYPE = 1
        AND A.DOCAMNT - ISNULL(B.AppliedAmount, 0) <> 0
        AND A.VOIDED <> 1


    Practically, the document amount should equal the applied amount (retrieved from the detail level).Otherwise, it will show a variance under the control amount field. In order to get this resolved, I moved the invoices from the history to open tables considering all associated history tables. This is yet under testing to confirm how valid such SQL solution is, so it is not quite recommended to do so if you don't have the know-how in SQL and GP tables.

    Your feedback is highly appreciated,

  • Community Member Profile Picture
    Community Member on at

    Hi Richard.  So should I remove the Transactions and the Distributions in my selection?  I selected report only to see what the results would be first and I received this error message: 'Unable to remove this transaction:  an open apply record exists or an apply record exists outside the selected range'.  

  • Community Member Profile Picture
    Community Member on at

    Hello Mahmoud.  thanks for your response however I am not well verse in running complex scripts so if something were to go wrong I would not know how to correct it.  Thanks for your feedback though.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,852 Moderator on at

    You will need to find this payment that has been applied to the invoice first and then void the payment. Now if invoice was fully applied to this payment you could just run paid transaction removal to move them both to history. If one is open and the other in history you will need to use the delete using Tools->Utilities->Purchasing->Remove Transaction History. Select the box to remove distributions as well. It may help to do this one invoice at a time leaving just the one(s) that have applied payments. This may get ugly so let me know how it goes and I will continue to assist.

  • Community Member Profile Picture
    Community Member on at

    The thing is there 'is no' payment.  The user's system did something that caused an interruption while she was attempting to apply a manual payment to these invoices.  When the system restored the payment was gone however these invoices are showing as applied and now in History.  Trying to remove these invoices gives me that error message I mentioned previously.

  • Suggested answer
    babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Hi Fonda,

    You said you have ran the check link have ran this all of payable transactions.  Also run reconcile 

    Purchasing >> Utilities >> Reconcile

  • Community Member Profile Picture
    Community Member on at

    yes I just inserted all after it didnt work for just the two logical 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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

#2
Shravan Attelli Profile Picture

Shravan Attelli 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans