Files in this folder address https://watsonexp.corp.adobe.com/#bug=3812493, Cleanup unnecessary data from PPS_PERMISSION_ACTIVITIES table. 

There are two approaches:
1. Perform cleanup in single run using INSERT-SELECT 
2. Process SCO-by-SCO using stored procedure

Approach (1) – select-insert 
-------------------------------
Need to be applied only once. There are no restrictions related to CPS version. Estimated time varies depending on hardware. In one example, it took about 6 minutes. 
By default, in order to delete activity account or SCO has to be disabled for at least one year. If you keep this default, you will need to re-run this script next year same time to fully filter activities.


Approach (2) - stored procedure 
-------------------------------
This approach is much lighter than previous. It works in the same way as approach (1) except single SCO at the time is processed. Can be applied once or scheduled to be executed in light load periods. Minimal recommended CPS version for efficient execution is 9.4. Estimated minimal time for processing 70M rows is about half an hour. This time depends on hardware, initial table size, load and parameters supplied. 

Script Delete_redundant_permission_activities_SP.sql is used to create stored procedure in main CPS database. Procedure can be executed any time. Processing is performed in batches. There can be pause after batch has been processed.

Stored procedure parameters are:
- @p_keepLastNdays, ommit SCO or account if disabled in last <@p_keepLastNdays>. Default is 365.
- @p_scoWaitSec, pause in seconds between batches. Default is 0.
- @p_batch, batch size. Default is 10. 
- @p_delCount, output parameter having total number of deleted rows in the last run
- @p_ScosProcessedCount, output parameter having number of processed SCOs in the last run
-------------------------------
There is no general rule which method is more appropriate for given database. 

APPENDIX
========
Number of rows qualified for keeping can be calculated. Criteria to keep activity is any of following:
- Both account and SCO are enabled
- SCO belongs to an account that was disabled up to 365 days ago but SCO was not disabled itself
- SCO was disabled up to 365 days ago

In other words: if account is disabled for at least 365 days OR if its SCO is disabled for at least 365 days,
its permission activities will be reduced

   DECLARE @moveDisabledBefore datetime
   SET @moveDisabledBefore = DATEADD(dd, -365, GETDATE())

   SELECT COUNT(*) FROM (SELECT
		  pa.SCO_ID, 
		  pa.ASSET_ID, 
		  pa.PRINCIPAL_ID, 
		  pa.ACTIVITY_TYPE_ID, 
		  MAX(PA.date_created) as DATE_CREATED
	 FROM  PPS_PERMISSION_ACTIVITIES pa
	 JOIN PPS_SCOS s ON pa.SCO_ID=s.SCO_ID
	 JOIN PPS_ACCOUNTS a ON A.ACCOUNT_ID=S.ACCOUNT_ID
	 WHERE 
		  (A.DISABLED IS NULL AND S.DISABLED IS NULL)
		  OR (A.DISABLED IS NOT NULL AND A.DISABLED > @moveDisabledBefore AND S.DISABLED IS NULL)
		  OR (s.DISABLED IS NOT NULL AND s.DISABLED > @moveDisabledBefore)
	 GROUP BY pa.SCO_ID, ASSET_ID, PRINCIPAL_ID, ACTIVITY_TYPE_ID) tmp