How do I delete commission history from Commission Plan?

Article ID: KB0064

Why would you want to delete commission history from Commission Plan tables? Although this is not a complete list, here are several reasons.

1. Commission Reports take a long time to print due to the large number of transactions being stored in Commission data tables.
2. Posting sales batches and/or transferring sales documents has slowed down over the years.
3. There is a large amount of commission data associated with inactive Salespeople and your company no longer needs any reference to this data.
4. Clean up your database.
5. Remove test data.
6. Your company was acquired by another company who requested the removal of all commission data referencing the acquired company.

Steps to delete commission history from Commission Plan tables

Check the client’s Matrix Type in the Matrix Options tab in the Commission Plan Setup window
[Sales > Setup > Commission Plan > Commission Plan Setup]:

 

Assuming that the client is NOT using the Quotas Matrix Type, please use the following instructions to remove all commission data for sales documents that fall on or before date YYYY-MM-DD:

1. In SQL Management Studio, run the following SQL script against the company database:
   
   DELETE ASI28301 WHERE DOCDATE <= 'YYYY-MM-DD'
   --replace YYYY-MM-DD with the greatest document date to delete; this script removes the commission header record, regardless of whether the commissions are processed

2. Make a backup of your Company Database.
3. Launch Dynamics GP and log into the company.
4. Open the Commission Plan Utilities window [Sales > Utilities > Commission Plan Utilities] and click the Commission Check Links button:

This will remove all commission data for orphaned commissions (the records related to the commission header records you removed in step 1). Check links will also remove unprocessed commission records for invalid sales documents (documents that were voided, orders that were transferred to invoices, etc). A report will print displaying these commission records – the orphaned commission records will NOT appear on the report, as it’s based on the commission header record that no longer exists.

4. Click the Go To button on the Commission Plan Utilities window and choose Summary Utilities – this will open the Commission Plan Summary Utilities window:
5. Ensure both check boxes are marked, and click the Process button. 
   This will rebuild the processed commissions summary data for each salesperson based on the processed commissions that remain in the system.