Remove SugarBPM Processes

Question

Does anyone have the SQL queries for removing SugarBPM process instances. I thought i had them but am unable to find them. 
 It would be nice if this was able to be done in the Data Archiver like Damien Pochon discussed: Data archiver: remove records older than x days

John Boyes · Answer

Here they are

-- pmse_email_message
DELETE em
FROM pmse_email_message em
INNER JOIN pmse_bpm_flow bf ON em.flow_id = bf.id
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_email_message
DELETE em
FROM pmse_email_message em
INNER JOIN pmse_bpm_flow bf ON em.flow_id = bf.id
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_email_message;

-- pmse_bpm_flow
DELETE bf
FROM pmse_bpm_flow bf
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_bpm_flow
DELETE bf
FROM pmse_bpm_flow bf
INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_bpm_flow;

-- pmse_bpm_thread
DELETE bt
FROM pmse_bpm_thread bt
INNER JOIN pmse_inbox i ON bt.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_bpm_thread
DELETE bt
FROM pmse_bpm_thread bt
INNER JOIN pmse_inbox i ON bt.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_bpm_thread;

-- pmse_bpm_form_action
DELETE bfa
FROM pmse_bpm_form_action bfa
INNER JOIN pmse_inbox i ON bfa.cas_id = i.cas_id
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_bpm_form_action
DELETE bfa
FROM pmse_bpm_form_action bfa
INNER JOIN pmse_inbox i ON bfa.cas_id = i.cas_id
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_bpm_form_action;

-- pmse_inbox
DELETE i
FROM pmse_inbox i
WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

-- pmse_inbox
DELETE i
FROM pmse_inbox i
INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
WHERE i.date_modified < '2023-01-01';

OPTIMIZE TABLE pmse_inbox;

André Lopes
Lampada Global

Skype: andre.lampada