Remove Vendor Accounts

[AKPolarBear]

After searching the Macola website and trying what they suggested, I still can't purge some vendor accounts. Actually, quite a few. The Macola error says files are still open but none of the Macola reports will show what. I have explored the data through Crystal and SQL query and cannot see anything that should be holding up deletion. I looked into the AP Trx, PO Headers, Open PO's, Alt address, recurring vouchers and what ever else I could see. I have tried to rebuild those files and or used the export/import process.

Am I missing a step somewhere or is this a problem for others as well?

I don't think I want to remove them manually from the APVENFIL table either.

A lot of these are 1 time vendors but do not the * in the vendor number.

Any other suggestions?

7.6.100a and MSSQL

 

[abaldwin]

Are you trying to do this while others are still in the system? They may have items open that reference this vendor.

Andy Baldwin

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

 

[AKPolarBear]

Yes, others were online but the Vendors are not in use and some have not been for several years. But, it's Macola so I will try that. They could have something hung open from an incomplete process, we just can't find it.

 

[kstickler]

We had the same problem. I finally figured it out by looking at POHDRHST & POLINHST and I found some orphaned records...headers with no line items. We tried the standard purging process but without line items it was like they did not exist. However, the vendor delete function must be referencing that POHDRHST file. I used SQL and manually deleted any historical PO with no corresponding line history for that vendor number.

I chalked it up to some conversion mishap from the past...

Hope this helps!

 

[abaldwin]

kimmay has my next line of thought. I would write two quick crystals. Look for orphaned headers (headers with no lines) but also look for orphaned lines (lines with no headers) It is amazing how often this happens both ways in macola.



Andy Baldwin

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

 

[AKPolarBear]

I did check the PO headers for no line items but did not check the line items for no headers.

I'll do that next.

 

[kstickler]

There are two sets of PO files: POORDHDR / POORDLIN and POHDRHST / POLINHST so be sure to check both.

 

[MacolaHelp]

Also poitmven file & imrechst may have the vendor # imbedded & they don't have on line purges, you have to edit in the db manager.

 

[dgillz]

The following are all the tables in macola that contain a field called 'vend_no':

APADJTRX_SQL
APALTADR_SQL
APDISFIL_SQL
APOPNFIL_SQL
APOPNHST_SQL
APRECDST_SQL
APRECVCH_SQL
APTRXFIL_SQL
APVENADT_SQL
APVENDST_SQL
APVENFIL_SQL
ARCUSADT_SQL
ARCUSFIL_SQL
BBOPNFIL_SQL
BCIMPHDR_SQL
BCPEXHDR_SQL
CHECKREC_SQL
CRDTLBL0_SQL
CRDTLBL1_SQL
CRDTLBL2_SQL
CRDTLBL3_SQL
FXACQAST_SQL
FXASTFIL_SQL
FXCNGAST_SQL
HZEQPFIL_SQL
HZMCOFIL_SQL
HZOCFIL_SQL
HZPPVFIL_SQL
IMATPORD_SQL
IMINVAUD_SQL
IMINVLOC_SQL
IMINVTRX_SQL
IMORDBLD_SQL
IMORDHST_SQL
IMRECHST_SQL
JOBHIST_SQL
MCDISFIL_SQL
MCTRXFIL_SQL
MCTRXHST_SQL
MRITMFIL_SQL
MRLWLFIL_SQL
MRREQFIL_SQL
MRVPW999_SQL
MSFRCFIL_SQL
MSSCHFIL_SQL
OELINAUD_SQL
OELINHST_SQL
OEORDLIN_SQL
POCOMCDE_SQL
POCOMFIL_SQL
POCONFIL_SQL
POCONHST_SQL
POCSHREQ_SQL
POHDRAUD_SQL
POHDRHST_SQL
POINSHDR_SQL
POITMVND_SQL
POIVAUD_SQL
POLINAUD_SQL
POLINHST_SQL
POLNDCST_SQL
POORDHDR_SQL
POORDLIN_SQL
POPRPVEN_SQL
POPURCST_SQL
POQTEHDR_SQL
POQTEPRE_SQL
POQTHDHS_SQL
POREQLIN_SQL
PORQLNHS_SQL
POTRXDST_SQL
POTRXFIL_SQL
SFDTLAUD_SQL
SFDTLFIL_SQL
SRDTLAUD_SQL
SRDTLFIL_SQL

I have a sql script that I use that will look for a given field name in all tables of a given SQL database. This comes in real handy in situations like this one.


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

http://www.gainfocus.biz

 

[AKPolarBear]

eeeuuughh...

You mean I would have to run that script for each vendor number in order to purge them?

Also coming my way will be a method to purge old PO's that are still kicking around for various reasons. That will be even worse of a project based on this.

 

[dgillz]

I am not sure you would have to delete from all these tables, but I thought you'd like to see the list. The Macola database has some referential integrity but I do not think it is that good!

Exact tech support or your reseller should be able to tell you all the tables that must be purged in order to allow deletion of a vendor.

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

http://www.gainfocus.biz

 

[kstickler]

That list is scary! Try the PO orphan checking route. When you use the standard Vendor delete process you will then know if you need to delve deeper into other files.