Criteria for deleting vendors in Accounts Payable:
Example -
- The current period in the AP module of the TEST database is 04/2017.
- Retention period/years set to 15 years for each.
- In order for vendors to be deleted as part of this process, they must have a zero balances and no activity since the vendor delete date.
- Accessed the Delete AP Detail screen to delete the detail and history and the vendors that have had no activity since 4/30/2002 (this date used since AP is still in 04/2017 in TEST).
- Before the process was ran the database had 8589 records in the Vendor table and after the process was ran it went down to 8573 records. Only 16 records were deleted.
A vendor can only be deleted if it doesn't have:
- any open documents
- a zero balance and
- has no records within the retention settings
When you run the Delete AP Detail Process with the option to 'Delete Vendors with No Activity Since' checked a vendor will not be deleted if it has apdocs, aptrans or aphist records within the retention settings. The process will check each vendor in the vendor table to see if they are eligible for deletion.
If you check the box to "Delete vendors with no activity since" and enter a date of 01/01/2018 (in other words, you want to delete all vendors with no activity since (01/01/2018) the system will check for the following:
1. Is the ap_balances.lastvodate less than or equal to 01/01/2018.
2. Is the ap_balances.lasstchkdate less than or equal to 01/01/2018.
3. Are both the ap_balances.currbal and ap_balances.futurebal equal to zero.
4. Are all of the current year and next year 1099 box totals (ap_balances.cybox00, cybox01, nybox00, etc.) equal to zero.
5. Are there any apdoc records for the vendor.
Some of these items are easiest reviewed within SQL Server Management Studio.
Some of these are duplicated from the above suggestions but if vendors fail to allow their deletion, then it is likely that they have documents that still exist for whatever reason. Below is a list of the tables that use the vendor ID. You could run these statements SQL Server Management Studio to see where the records may exist. Edit/Replace xxx with the vendor ID in question.
select * from AP_Balances where VendID = 'xxx'
select * from APAdjust where VendId = 'xxx'
select * from APCheck where VendID = 'xxx'
select * from APDoc where VendID = 'xxx'
select * from APHist where VendID = 'xxx'
select * from APTran where VendID = 'xxx'
select * from Vendor where VendID = 'xxx'
select * from LCReceipt where VendID = 'xxx'
select * from LCVoucher where VendID = 'xxx'
select * from POAddress where VendID = 'xxx'
select * from POReceipt where VendID = 'xxx'
select * from POReqHdr where VendID = 'xxx'
select * from POTran where VendID = 'xxx'
select * from POVendReqSum where VendID = 'xxx'
select * from PurchOrd where VendID = 'xxx'
select * from SalesTax where VendID = 'xxx'
select * from VendItem where VendID = 'xxx'
select * from VendorPay where VendID = 'xxx'
select * from VendorRebate where VendID = 'xxx'