We are currently on GP 2010. We have thousands of PO's that have been integrated into our system (either created incorrectly or as duplicates) that need to be removed. Is there a way to mass close these PO's? It would be inefficent to do these one at a time as GP currently allows.
*This post is locked for comments
Fonda,
You can run a macro that you create through a mail merge.
A macro is just a text file. When you record a macro in GP, it creates a .mac file that you name. The file can be modified in any text editor (Word, Notepad,etc.)
The basic idea is to create a macro to close one PO and then modify the macro by copying the lines of the original macro and changing the PO number for each iteration.
These are the steps:
First, create an Excel worksheet with the list of PO's you want to close. I used the canned Smartlist received-not-invoiced and added a date cutoff. Export to Excel and save it as a .txt file. It will look like this:
PO Number
'091214'
'091214-1'
091714-2'
'092214MARY'
etc.
Then, in Word, create a mail merge. This link has step-by-step instructions:
blogs.msdn.com/.../how-to-use-word-mail-merge-and-macros-to-import-data.aspx
My Word document looked like this:
TypeTo field 'PO Number' , '091214'
MoveTo field 'PO Status' item 0
ClickHit field 'PO Status' item 3 # 'Closed'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'
These are the first lines of the macro that was created through the mail merge:
# DEXVERSION=12.00.0276.000 2 2
ActivateWindow dictionary 'default' form 'POP_Edit_PO_Status' window 'POP_Edit_PO_Status'
TypeTo field 'PO Number' , '091214'
MoveTo field 'PO Status' item 0
ClickHit field 'PO Status' item 3 # 'Closed'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'
TypeTo field 'PO Number' , '091214-1'
MoveTo field 'PO Status' item 0
ClickHit field 'PO Status' item 3 # 'Closed'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'
TypeTo field 'PO Number' , '091714-2'
MoveTo field 'PO Status' item 0
ClickHit field 'PO Status' item 3 # 'Closed'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'
TypeTo field 'PO Number' , '092214MARY'
MoveTo field 'PO Status' item 0
ClickHit field 'PO Status' item 3 # 'Closed'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'Etc.
Note that I had to omit the first 2 lines when I created the mail merge so they wouldn't keep repeating (that made the macro crash). I added those 2 lines to the beginning of the macro after the mail merge document was created.
Once the PO's are closed they can be removed to history. I used the same source file and created another macro. This was the macro for the first 2 PO's:
# DEXVERSION=12.00.0276.000 2 2
ActivateWindow dictionary 'default' form 'POP_Remove_Completed_PO' window 'POP_Remove_Completed_PO'
MoveTo field '(L) From PO Number'
TypeTo field '(L) From PO Number', '091214'
MoveTo field '(L) To PO Number'
TypeTo field '(L) To PO Number', '091214'
MoveTo field 'Insert Button'
ClickHit field 'Insert Button'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'
NewActiveWin dictionary 'DEX.DIC' form 'Report Destination' window 'Report Type'
MoveTo field '(L) Cancel'
ClickHit field '(L) Cancel'
NewActiveWin dictionary 'default' form 'POP_Remove_Completed_PO' window 'POP_Remove_Completed_PO'
NewActiveWin dictionary 'default' form 'POP_Remove_Completed_PO' window 'POP_Remove_Completed_PO'
MoveTo field '(L) From PO Number'
TypeTo field '(L) From PO Number', '091214-1'
MoveTo field '(L) To PO Number'
TypeTo field '(L) To PO Number', '091214-1'
MoveTo field 'Insert Button'
ClickHit field 'Insert Button'
MoveTo field 'Process Button P'
ClickHit field 'Process Button P'
NewActiveWin dictionary 'DEX.DIC' form 'Report Destination' window 'Report Type'
MoveTo field '(L) Cancel'
ClickHit field '(L) Cancel'
Of course I strongly urge you to run the macro on a small sample in a test database, and then in full on the test database, before you run it in your live company as the changes are difficult to undo.
Good luck! This saved us many hours of keypunching…
definitely when we talk about utility to do some task it doesn't means only to turn flag from one status to another status, there is lot to care about.
Hi Fonda,
There is much more involved in the PO closing than just changing the flag from one status to the other (i.e Released to Closed).
You should start by doing a clean-up of all closed PO's that you want to move to history (see reply above). Once done, you should asses the PO's that are still needed (i.e. New, Released, Change Order or Received). Work by dates going back to the inception of your GP installation. Be carefull about the "Move PO to history" process, because if you never did it before, it will be quite long, so go by batches of 3-6 months, otherwise you're going to kill the system and your patience. Do it off-hours when nobody is working in the system. Take backups before you start and run reports on regular basis.
Use the provided Excel reports to asses all the POP documents in your system. They were built with ease of read in mind (i.e. human readable field names, not strange acronyms).
The 2 main tables that are involved in the POP work process are POP10100 and POP10110, but there are a few others to consider. Mainly those 2 tables hold the status field (at PO header and Line Item level). Here is SQL script that I grabbed somewhere on internet (can't remember where) to get the essential information out of the POP tables :
SELECT
CASE POL.POLNESTA
WHEN 1 THEN 'New'
WHEN 2 THEN 'Released'
WHEN 3 THEN 'Change Order'
WHEN 4 THEN 'Received'
WHEN 5 THEN 'Closed'
WHEN 6 THEN 'Canceled'
END PO_Line_Status,
CASE RWH.POPTYPE
WHEN 1 THEN 'Shipment'
WHEN 2 THEN 'Invoice'
WHEN 3 THEN 'Shipment/Invoice'
END PO_Type,
RWH.VENDORID AS 'Vendor ID',
RWH.VENDNAME AS 'Vendor Name',
RWH.VNDDOCNM AS 'Vendor Doc #',
RLQ.POPRCTNM AS 'RCT #',
POL.PONUMBER AS 'PO #',
POL.LineNumber AS 'PO Line #',
RLQ.QTYSHPPD AS 'QTY Shipped',
RLQ.QTYMATCH AS 'QTY Matched',
RLH.EXTDCOST AS 'Line Extended Cost',
RWH.Total_Landed_Cost_Amount AS 'Total Landed Cost',
RWH.BACHNUMB AS 'Batch ID',
RLH.LOCNCODE AS 'Site ID',
RWH.POSTEDDT AS 'Posted Date',
RWH.receiptdate AS 'Receipt Date',
RWH.GLPOSTDT AS 'GL Posting Date',
RLH.ITEMNMBR AS 'Item #',
RLH.VNDITNUM AS 'Vendor Item #',
RLH.ITEMDESC AS 'Item Description',
RLH.VNDITDSC AS 'Vendor Item Description',
RLQ.QTYINVCD AS 'QTY Invoiced',
RLH.UOFM AS 'U of M',
RWH.SUBTOTAL AS 'Receipt Subtotal',
POL.ProjNum AS 'Project Number',
POL.CostCatID AS 'Cost Category'
FROM
POP10110 POL,
POP10500 RLQ,
POP30300 RWH,
POP30310 RLH
WHERE
RLQ.PONUMBER = POL.PONUMBER AND RLQ.POLNENUM = POL.ORD AND RWH.POPRCTNM = RLQ.POPRCTNM AND RLH.POPRCTNM = RWH.POPRCTNM AND RLH.POPRCTNM = RLQ.POPRCTNM AND RLH.RCPTLNNM = RLQ.RCPTLNNM AND RLH.PONUMBER = POL.PONUMBER AND RLH.PONUMBER = RLQ.PONUMBER AND (POL.POLNESTA Not In ('5', '6')) AND ((RLQ.QTYSHPPD - RLQ.QTYMATCH) <> '0')
ORDER BY RWH.VENDORID, RWH.VNDDOCNM, POL.PONUMBER,
POL.LineNumber
Adapt it to your needs as you go. The PO Line status can be different from the PO Header status... but generally speaking, when all the Line Items are closed, the PO should be closed too.. and the move process will transfer them to the historical tables (POP30100 & POP 30110).
Good luck.
Plz email me at almas@evinciblesolutions.com so will discuss further.
Thanks for your feedback SK. It's not just about removing them as I want to do that as well but I need to change the PO status to 'Closed'. Since there are so many I was wondering if there is a way to mass close these instead of one at a time the GP standard permits.
Thanks Almas. I would be interested in the utility if you could tell me more about it. Also Among these 'thousands' of PO's are not only the ones created in error and duplicates but some are actually valid but due to the age of them we just want to close them. Therefore ALL of these PO's we want to change the status of these as 'Closed' at once and not the GP standard way of one at a time. That would take way too long to get done.
Remove completed order window will help you to remove sequence number of PO numbers. If you want to remove random PO numbers, then you would follow Almas suggestions. Before doing this from any 3rd party utility or backend operation make sure you have a latest GP backup.
hope this helps!!!
If you like you could contact me for custom utility.
In my opinion, since you have POs that have been integrated into your system, they must not always be of canceled or closed status. In this case you need a small custom utility to just delete those POs from GP.
This is a two step process performed in the Purchasing > Routines > Remove Completed Purchase Orders window if you are keeping purchase order history.
Use the Remove Completed Purchase Orders window to move closed or canceled purchase orders to history if you are keeping purchase order history. If you aren't keeping purchase order history, the purchase orders will be removed from the system.
The Completed PO Removal Report will print after the process has completed. If a purchase order won't be removed (for example, if it is in use or has damaged data), a message will print on the Completed PO Removal Report indicating that the purchase order wasn't removed.
After you've transferred the completed purchase orders to history, you can use the Remove Purchasing History window to delete purchase order history or print the Purchase Order Trx History Removal Report before removing history.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (