Clean up Open A/P Files 3

[arnold2002]

How can I clean up the open AP file when I have invoices with dates like 10/25/2020. These invoices have also been paid so there is an ending balance of zero. Exact told me to void out the checks and then delete the invoices BUT this did not work because of the invoice date.

I want to extract the table, delete the zero balance items and upload the table again. Exact will not help me.

PLEASE HELP ME.

 

[pronet74]

What version of macola are you on?

 

[dgillz]

Assumming you are running progression, lets get first things first, go to System Manager, Maintain, system periods and maintain the AP record -- and for that matter all records -- to not accept dates not in the current year.

Then to fix the existing AP open items, go to AP, Maintain, Open Item Load, and change the date.

This will not fix the APDISFIL however, which will still have 2020 dates. The only way to fix this is a SQL query by means of enterprise manager, pervasive control center, or MS Access.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[arnold2002]

I have exported files to access before, removed the record, and then imported them back but this was at another company. I have not attempted it here.

I changed the field not to allow dates from another year.

Can you tell me how to extract the table into access, clean it up, and teh upload it again?

 

[abaldwin]

You should not have to export the files to Access. You only need to create a link in Access to the Macola table.

WARNING WARNING. Even though Don is right, I must remind you to be VEEERRRRRY careful modifying tables unless you are very familiar with the processes and other effected tables.


Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[arnold2002]

Thanks, I still need to know how to do it.

 

[maccrystal]

Make a backup or export the table through System manager. If you are not comfortable, ask your reseller for help.
You will need to check the APOPNFIL_SQL and APOPNHST_SQL

Use a select statement to find the record and confirm the field(s) that need to be changed.

Select * From APDISFIL_SQL where Trx_dt=20201015

Once you find the record and confirm what needs to be changed. The where clause restricts what is updated.

Update APDISFIL_SQL Set Trx_dt=20021015, doc_dt=20021015 where Trx_dt=20201015

You can alway export the table to a text file and correct it with UltraEdit and re-import it.

Just be certain you have a GOOD BACKUP

http://www.appstratinc.com

 

[MacolaHelp]

You need to deal with BOTH the check records and the invoice records in apopnfil, apopnhst, apdisfil and bbopnfil if you are using bankbook (if you want to DELETE the zero balanace records). You need to do both the invoice and the payment transactions, otherwise you'll leave payment records with no invoices which will create yet another problem.

Exact's recommendation was improper because you did really get these invoices and really did pay them, therefore they should not be deleted.

If you use open item load to change the invoice dates to the correct dates, you will have good info in apopnfil & apopnhst. It sounds like the check date was correct, so no worry in bankbook. That leaves apdisfil to correct. Did you post the 2020 voucher transactions to GL at some point & correct the dates in GL or in a GL transaction session? You can verify if ever posted by running AP distribution to GL including interfaced transactions & see if there is an * next to the transactions. If so, you probably have corrected in GL already. If so, I would just leave it alone since you've documented the original activity and the correction in your system of record, which is better if you ever have to deal with an audit. Taking records out through the back door leaves no audit trails. Unless you document what you've done, you can create suspicion about the validity of your accounting records.

If you do use open item load to change the invoice dates, you should run reset fully paid items, reset vendor account balances and age vendor accounts. Double check your open item reports to be sure the transactions now have the correct dates. With the correct dates, these transactions can now be purged if needed since the records net to zero and are before a particular date you specify on the purge.