Sage Pro Archive “Un Do” Button?
If you use Sage Pro, one of the benefits is that when you close any financial period, you have the choice to move completed financial transactions to the archive files. Completed transactions in Sage Pro are defined as invoices fully paid, sales order(s) completely shipped, cash receipts fully applied and purchase orders completely received. Sage Pro, depending on the version, tracks transactions using an internal column called, “CURRHIST.” Sage Pro moves these completed transactions from current files to the history or archive files. There are five major history files per module (A/P, S/O, P/O, A/R, JC, and PR). In Sage Pro 7.6 and 7.7, the number of history files increased.
Starting with Sage Pro 7.5, Sage introduced a new archive and period close table called, “SYSAPMNT.” Sage inserts another entry into this table when you close any period and has a column with your selected archived date. This new column of archived date is used by Sage Pro on all reports since Sage Pro knows when you last selected to archive and the date that you used.
So what happens when you select to close the period, select “Yes” to archive and enter the default date of today?
Can’t you just “un do”?
Short Answer: No!
Hopefully, you just backed up your system prior to closing the period and archiving. Just restore your backend and reclose your month.
But what if I did not backup before closing my period and archiving?
Long Answer: First you need to know if your Sage Pro installation is a Visual FoxPro (VFP) or SQL installation. Since Sage Programmer moved all data from current tables into history tables, you can move all history table data back into current data tables but it is going to take programming. Get everyone out of the system and backup now! Then we can start.
If it is SQL, then with 2 commands per table, you can pull back all desired history data back into the current tables and remove that same data from the history tables. With VFP, it is going to take more commands per file; you have to “USE”, “SET FILTER TO”, “Copy to”, “Append from” and then “delete with filter”. With Sage Pro 7.5 or newer, Visual Fox Pro can actually transact SQL commands against a VFP database and yes you key the commands using exact SQL command syntax but you do have to use the VFP “SELECT 0” and “USE” commands with an “ALIAS” qualifier that you then reference in VFP SQL statement.
Example: move any history transactions for company # 99 beginning January 1, 2015 back into current invoice master file
Sage Pro System Manager [File] – [Open] – [Foxpro Commands]
Use PRODATA!ARYMST99 alias ARYMST99
Use PRODATA!ARMAST99 alias ARMAST99
INSERT INTO ARMAST 99 Select * from ARYMST99 where invdte > CTOD( ‘12/31/2014’)
DELETE ARYMST99 where invdte > CTOD(‘12/31/2014’)
Now just repeat for all the other current and history file pairs.
Is it that simple? No! Sorry, it is not.
Sage Pro keeps other history tables for address and notes on orders related to the current address tables but does not date stamp them. That means for these files you have to match either on customer plus invoice or sales order number or match on vendor plus invoice or purchase order number. Here is the SQL statement for pulling the addresses for invoices
Use PRODATA!ARYADR99 alias ARYADR99
USE PRODTA!ARADDR99 alisa ARADDR99
Insert into ARADDR99 SELECT a.* FROM aryadr99 a INNER JOIN arymst99 b ON a.custno+a.invno=b.custno+b.invno where a.custno+a.invno=b.custno+b.invno and b.invdte > ctod(’12/31/2014′)
You need to repeat this special select for each address table. There is one address table for each of the modules A/P, A/O, A/R and P/O.
All this is not difficult, it is time consuming. Test your code carefully and make sure that you have a good backup.