How to manipulate accounts receivable statements 1

[IzakL]

From my own experience and questions asked in this forum, I have written the following as a sort of step by step guide. I am also by no means a sql wizard or Accpac master.
This was just a means to an end for me.

SUMMARY OF MY PROBLEM:
I am using Sage Accpac 200 ERP (Version 5.4A Service Pack 3)

I had a customer "A0036" who have bought from our company from 2003 until 2005. They then stopped to buy from us until this month when they started buying from us again.

When the statements were printed for customer "A0036", it showed all transactions from 2003 until present. We previously used Open Item Statements and 3 months ago changed to Balance Forward Statements.

As far as my knowledge goes, when you print a Balance Forward Statement and accept the statements as correctly printed, Accpac sets certain fields in the database table. When these fields are set and you print the next month’s statement, the previous month’s transactions will not show again. In Open Item Statements, this is not the case. It will only show the items which is still outstanding.

When I printed the Customer statements, including "A0036", I accepted the statements a printed correctly and Accpac subsequently set the fields in the database so that the transactions will not print again.

To correct my problem I had to do the following:

Firstly, I had to reset all the customer transactions as if no statements have ever been printed.
This was done using the SQL Statements below:

UPDATE ARCUS SET DATELASTST = '0' WHERE IDCUST = 'A0036';				
UPDATE ARCUS SET AMTLASTSTT = '0.000' WHERE IDCUST = 'A0036';
UPDATE ARCUS SET DTBEGBALFW = '0' WHERE IDCUST = 'A0036';
UPDATE ARCUS SET AMTBALFWDT = '0.000' WHERE IDCUST = 'A0036';
UPDATE AROBP SET DATELSTSTM = '0' WHERE IDCUST = 'A0036' AND DATEBUS >= '20030101' and DATEBUS <= '20100831';
UPDATE AROBL SET DATELSTSTM = '0' WHERE IDCUST = 'A0036' AND DATEBUS >= '20030101' and DATEBUS <= '20100831';

Line 1 Updates the ARCUS Table and sets the Last Statement Date field to Zero for the specified customer
Line 2 Updates the ARCUS Table and sets the Last Statement Balance to Zero for the specified customer
Line 3 Updates the ARCUS Table and sets the Balance Forward Date field to Zero
Line 4 Updates the ARCUS Table and sets the Balance Forward Balance to Zero
Line 5 Updates the AROBP Table and sets the Last Statement Date to Zero for the specified customer AND for all dates that the customer had transactions
Line 6 Updates the AROBL Table and sets the Last Statement Date to Zero for the specified customer AND for all dates that the customer had transactions


With the statement details set to Zero, I could now print a statement show all the transactions again.

Secondly, I had to set the customer transaction that I do not want to show, as printed. As I only wanted transactions for 2010-08-01 to 2010-08-31 to show, I had to set the date of the last statement as 2010-07-31. And the customer had no opening balance amount as they paid all amounts owing in 2005.
This was done using the SQL Statements below:

UPDATE ARCUS SET DATELASTST = '20100731' WHERE IDCUST = 'A0036';
UPDATE ARCUS SET AMTLASTSTT = '0.000' WHERE IDCUST = 'A0036';
UPDATE ARCUS SET DTBEGBALFW = '20100731' WHERE IDCUST = 'A0036';
UPDATE ARCUS SET AMTBALFWDT = '0.000' WHERE IDCUST = 'A0036';
UPDATE AROBP SET DATELSTSTM = '20100731' WHERE IDCUST = 'A0036' AND DATEBUS >= '20030101' and DATEBUS <= '20100731';
UPDATE AROBL SET DATELSTSTM = '20100731' WHERE IDCUST = 'A0036' AND DATEBUS >= '20030101' and DATEBUS <= '20100731';

Line 5 sets all the transactions between the first date and the specified Last Statement Date.
Line 6 sets all the transactions between the first date and the specified Last Statement Date.


I think that the above statements could also be useful if you printed statements in Balance Forward, accepted them as correct and then made changes to 1 or more accounts and wanted to reprint the customer statement showing all the month’s transaction and not just the new entries on the account.

I am sorry if this is long and extended. This is my first tip and I tried to explain the problem as thoroughly as possible.

Regards,
IzakL