Remove SugarBPM Processes

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  discussed: Data archiver: remove records older than x days

Parents
  • 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
  • Hi,

    be carefull because with these queries all BPM that have an entry condition with "on first update" because it relies on "pmse_bpm_flow" content to know if the BPM has been trigger once or not.

  • -- 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'

    AND bf.id not in* <put in exclusive sql by condition here>

Reply Children
No Data