Delete useless SubstitutionRules

When using SAP Business Process Management everyone who participates in a BPM workflow should maintain substitution rules, either for sickness (unplanned) or vacation (planned) absence, to e.g. finalize approval tasks before a deadline. A workflow sitting unapproved in the inbox of a manager who is on a month-long vacation could be a problem for the organization or an employee. To address absence issues, SAP BPM provides a substitution mechanism. The manager/approver nominates a substitute for the duration of his or her absence.

The problem is:

When I am substituting someone who left the company, his or her substitution rule will stay in the system. Only the substituted person, who is the owner of the rule, can delete it. But when his or her user is not available anymore this rule will remain in the database without any use. But I always want to keep my system tidy!, so obviously, I do need some functionality that deletes all substitution rules which are missing either the substituted or the substituting user.

I think of implementing some EJB function or job to realize this cleanup task, so I first look at the standard BPM implementation. The interface SubstitutionRuleManager, which is available via the BPMFactory, is the single point of accessing substitution rules, so I call its method deleteRule() in my coding and pass the substitution rule that shall be deleted. Unfortunately I get a runtime error saying I am not allowed to delete someone else’s rule, and it turns out that this is not a question of missing UME actions respective rights but is a fact of design. So how should I ever be able to delete a rule of a user that is not there in the UME anymore?

Let’s have a look at the data itself. I can browse the content of all substitution rules in the database when I open the SQL Data Browser (in NetWeaver Administrator goto Troubleshooting, Database,  Open SQL Data Browser) and open the table BC_BPEM_TM_SUBST. Here I can gather many information regarding all existing substitution rules, but the user IDs from both the substituted and the substituting user are encrypted. So I’m not able to identify deleted users, pick the counterpart’s user id from the database table to read his/her rules by an API call to subsequently delete that rule with the empty counterpart. What also would be nice is if there would be an empty value for the deleted user, so I would be able to identify the record with the deleted user, but the encrypted ID remains in this database table even though a user has been deleted.

It seems that I don’t have any chance to express some SQL statement that can run over the table and find all the rules containing deleted users. You cannot even read all substituted and substituting users from the database table, lookup their users in the UME and see if they have been deleted, because the users are encrypted in the database and a match to UME users is out of sight, because you don’t know the decryption algorithm.

It seems that there is no way to solve my problem; useless rules with deleted users will stay in the system forever.

Really? Let’s see what the BPM API can do for us. Again I think of the SubstitutionRuleManager serving such a purpose. One method attracts my notice: getRulesBySubstitutionProfile(URI substitutionProfileId). All the rules that I can create from the BPM Inbox or my custom inbox don’t have a rule profile. Looking into the database table confirms: no rule has an entry in the profile column. So my first try is a method call with null as parameter value. No success. Okay, but there seems to be light at the end of the tunnel.

 The solution for my problem is this:

Write a job that reads all BPM users and then reads their substituted and substituting rules. If the counterpart is not a real user but the deleted user the rule can be put into a HashSet for later deletion. All rules that remain without a profile have neither a substituted nor a substituting user; they can easily be identified by an SQL statement that selects all entries where the profile ID is null. These ones are also added to the HashSet, which then can be run through. Every rule from the HashSet can definitely be deleted with the rule manager.

Perfect. All useless rules have been deleted.

Problem solved.

Thanks & Regards,

Jan-Henrik