Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Mass close old Purchas...
Finance | Project Operations, Human Resources, ...
Suggested answer

Mass close old Purchase Order - Effects and SQL

(0) ShareShare
ReportReport
Posted on by shai4444

Hello,

I got a request from AP user that handles Purchasing to see if there is a way to Mass close older Purchase Orders. She has thousands of them from 2010 to 2015.

I haven't seen much about this in this community and understand there is no Internal tool within GP. 

1. I am wondering what takes place as far as process when the Purchase Order is closed. Are the POP10500 – Receipt Line Quantities table gets updated? Any GL tables gets updated?

2. I am wondering if I can just mass update the Purchase Order Header and Detail tables directly? Would this be enough?

Thanks in advance,

Shai 

Categories:
Microsoft Dynamics GP
  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Mass close old Purchase Order - Effects and SQL

    In this case your only way to proceed would be to close the PO's thru the Edit PO option.. Once closed, they can be moved to history.

    If there are several hundreds of them, the Macro approach is the most efficient.

    Have a look at this on how to do it with Word: www.encorebusiness.com/.../

    or with Excel : community.dynamics.com/.../microsoft-dynamics-gp-macros-macro-by-excel-formula

  • shai4444 Profile Picture
    shai4444 on at
    RE: Mass close old Purchase Order - Effects and SQL

    The current status of POs is 'Released'. If I understand correctly, the Purchase Order would close by itself once it is Invoiced all shipped quantities. Looks like all the POs that needs close are ones that have some amounts still left on them. We have a company policy not touch the back-end of GP if any item related was Posted so I am going to have to let this question stay since I won't be able to follow up on the resolution. I think that we do use Accrual accounts but most likely they are cleared in year end since these are POs from 2015 and older.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Mass close old Purchase Order - Effects and SQL

    I would try looking at one of these POs you want to remove. Look in the POP10100, POP10110, POP10500,POP10550 and POP10600 tables and look at the quantity fields. I would think there would be more to moving POs to history than just changing the status. What is the current status of this PO? If it is not closed, why not? Moving POs to history will not affect the GL. However if you have an accrual account in your GL you may run into issues with POs that were shipped but not invoiced.

  • shai4444 Profile Picture
    shai4444 on at
    RE: Mass close old Purchase Order - Effects and SQL

    For these Purchase Orders in question we do not keep Inventory, so I am not concerned about the Inventory Module. I am not sure if out accounting department uses the Purchase Receipt Report. Looks like it is tied to Inventory as well. I am familiar with the POP10500 and POP10600 tables but for PO that are Inventory controlled. I am trying to narrow down if there would be any effect for a non-inventory Purchase Order except for the PO header and detail tables. Any relationship to the GL tables?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Mass close old Purchase Order - Effects and SQL

    Just changing the status will not move them to history nor affect the quantity on order on the item vendor card. There there is the issue with the IV10200 and SEE30303 tables. Not to mention the POP10500 and POP10600 tables and others. Do you use the purchase receipts report? PO receipts are recorded in that table.  I would create a TEST company and run the script and then run reports to see if there are any ill effects to simply flipping the flag. Running Remove Completed POs and then IV Reconcile will be the definitive answers.

  • shai4444 Profile Picture
    shai4444 on at
    RE: Mass close old Purchase Order - Effects and SQL

    Richard, just to be clear, these are Purchase Orders that are older than 4 years. All the AP clerk is doing right now is to set the Status to 'Closed' using the 'Edit Purchase Order' window. She informed me that there is no concern for any open received and/or Invoiced quantities. I am wondering if I can do a mass update on the POP 10100 and 10110 tables or there is something else that takes place in the application when the PO status is set to Close.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Mass close old Purchase Order - Effects and SQL

    You would need to take into consideration the receipt quantities. So if the quantity ordered is 10 and you received 10 and were invoiced for 10 then you can change the status on the line to closed. If you received anything less then you can cancel the line. A problem would be if the shipment received does not equal the invoice quantity, then what do you do? If you simply want to fry them all and move them to history I would change the status to either completed or cancelled. If all has been shipped and invoiced set it to closed otherwise set it to cancelled. After you have ran Removed Completed Purchase Orders I would run IV Reconcile to correct the quantity on order.

  • shai4444 Profile Picture
    shai4444 on at
    RE: Mass close old Purchase Order - Effects and SQL

    I am trying to avoid using the Edit PO status window. There are many Thousands of POs that need closed. I am wondering what is the effect of changing their status directly in SQL.

  • twelvestrikes Profile Picture
    twelvestrikes 3,657 on at
    RE: Mass close old Purchase Order - Effects and SQL

    Hi Shai

    Just to add to the advice that Beat has provided you, if you have quite a number of PO's that have line items that need to be cancelled, creating a macro to do this is a good way to go.

    I had a client that one of my colleagues zero'd out the remaining quantities in SQL.  When checklinks was run on the POP tables those PO's came back to life.

    Creating a macro and cancelling the PO's using the Edit PO status window is the correct way to resolve this.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Mass close old Purchase Order - Effects and SQL

    sounds good.

    You can only close a PO before moving it to history when all the items have been received.. If some PO's are partially received only, you'll have to cancel the remaining quantities in order to close the PO (or complete the receiving).

    This could help (with a SQL script to list remaining quantities)

    https://community.dynamics.com/gp/f/microsoft-dynamics-gp-forum/151119/po-remaining-balance-quantity

    In newer GP versions there is now a 'tolerance' settings that can be setup in the POP module to allow for under- or over-receiving quantities. PO line items would be considered as completed if falling within the tolerance settings. 

    https://trinsoft.com/blog/2016/12/29/tip-quantity-tolerances-in-purchase-order-processing-in-microsoft-dynamics-gp/

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 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